dynamic port connection

Hi,
What is your question?
Sincerely,
Invotion Engineering Team
Advanced Microsoft Hosting Solutions
http://www.Invotion.com
>--Original Message--
>We have a customer who uses the dynamic port assignment
option when a connection is established, instead of the
default 1433. However, our application requires a fixed
port number, either the default 1433 or an explicitly set
port number.
>For now we are accessing this small database via the
ODBC connector using the Microsoft-supplied ODBC for
SQLServer driver. However, this is not a solution for
larger databases because of the additional overhead
involved.
>Any assistance will be very appreciated.
>Alex Ivascu
>(posted to server ng, by mistake)
>.
>This particular database server has multiple instances, and one of them is t
he one that we need to connect to. How do we know what port number to conne
ct to, with our application? Since the only things that we can specify in o
ur connector is a port numb
er and a server name...
Thanks.|||If the application is an ODBC application using MDAC 2.6 or greater, we'll
enumerate the ports and connect on the right one.
If you don't want that behavior, then set the ports to a fixed port and use
this in your connection string.
823938 How to use static and dynamic port allocation in SQL Server 2000
http://support.microsoft.com/?id=823938
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

dynamic port connection

Hi,
What is your question?
Sincerely,
Invotion Engineering Team
Advanced Microsoft Hosting Solutions
http://www.Invotion.com
>--Original Message--
>We have a customer who uses the dynamic port assignment
option when a connection is established, instead of the
default 1433. However, our application requires a fixed
port number, either the default 1433 or an explicitly set
port number.
>For now we are accessing this small database via the
ODBC connector using the Microsoft-supplied ODBC for
SQLServer driver. However, this is not a solution for
larger databases because of the additional overhead
involved.
>Any assistance will be very appreciated.
>Alex Ivascu
>(posted to server ng, by mistake)
>.
>
This particular database server has multiple instances, and one of them is the one that we need to connect to. How do we know what port number to connect to, with our application? Since the only things that we can specify in our connector is a port numb
er and a server name...
Thanks.
|||If the application is an ODBC application using MDAC 2.6 or greater, we'll
enumerate the ports and connect on the right one.
If you don't want that behavior, then set the ports to a fixed port and use
this in your connection string.
823938 How to use static and dynamic port allocation in SQL Server 2000
http://support.microsoft.com/?id=823938
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

dynamic port assignment

We have a customer who uses the dynamic port assignment option when a connec
tion is established, instead of the default 1433. However, our application
requires a fixed port number, either the default 1433 or an explicitly set p
ort number.
For now we are accessing this small database via the ODBC connector using th
e Microsoft-supplied ODBC for SQLServer driver. However, this is not a solu
tion for larger databases because of the additional overhead involved.
Any assistance will be very appreciated.
Alex IvascuYou can have SQL Server listen on multiple ports. So if the application
chooses between a list of ports you could simply have SQL listen on all of
them. Of course that is not the optimum solution for security reasons and
your firewall folks will probably not like the idea at all...
To configure SQL to listen on multiple ports; open up the Server Network
Utility, open up the TCP/IP properties and in the ports textbox enter in
your ports separated by commas.
"alexdivascu@.hotmail.com" <anonymous@.discussions.microsoft.com> wrote in
message news:8847E4B5-8BC9-4725-86F8-976AF93F945E@.microsoft.com...
> We have a customer who uses the dynamic port assignment option when a
connection is established, instead of the default 1433. However, our
application requires a fixed port number, either the default 1433 or an
explicitly set port number.
> For now we are accessing this small database via the ODBC connector using
the Microsoft-supplied ODBC for SQLServer driver. However, this is not a
solution for larger databases because of the additional overhead involved.
> Any assistance will be very appreciated.
> --
> Alex Ivascu
>|||So... how do people handle connecting to a particular instance, since the po
rt numbers are dynamic? Do they put a "list" of ports to try?
Thanks, Don.
Alex Ivascu|||Now I think I know what you are talking about. In the Client Network
Utility in the Alias setup, if the "Dynamically Determine Port" box is
checked there is no additional setup needed on your servers.
That setting just means that the client will query the instance to determine
which port should be used and then use it.
HTH
"Alex Ivascu" <anonymous@.discussions.microsoft.com> wrote in message
news:D3956B77-CC1F-47D6-95A5-0F7595BA76D2@.microsoft.com...
> So... how do people handle connecting to a particular instance, since the
port numbers are dynamic? Do they put a "list" of ports to try?
> Thanks, Don.
> Alex Ivascu
>sql

dynamic port assignment

We have a customer who uses the dynamic port assignment option when a connection is established, instead of the default 1433. However, our application requires a fixed port number, either the default 1433 or an explicitly set port number.
For now we are accessing this small database via the ODBC connector using the Microsoft-supplied ODBC for SQLServer driver. However, this is not a solution for larger databases because of the additional overhead involved.
Any assistance will be very appreciated.
Alex Ivascu
You can have SQL Server listen on multiple ports. So if the application
chooses between a list of ports you could simply have SQL listen on all of
them. Of course that is not the optimum solution for security reasons and
your firewall folks will probably not like the idea at all...
To configure SQL to listen on multiple ports; open up the Server Network
Utility, open up the TCP/IP properties and in the ports textbox enter in
your ports separated by commas.
"alexdivascu@.hotmail.com" <anonymous@.discussions.microsoft.com> wrote in
message news:8847E4B5-8BC9-4725-86F8-976AF93F945E@.microsoft.com...
> We have a customer who uses the dynamic port assignment option when a
connection is established, instead of the default 1433. However, our
application requires a fixed port number, either the default 1433 or an
explicitly set port number.
> For now we are accessing this small database via the ODBC connector using
the Microsoft-supplied ODBC for SQLServer driver. However, this is not a
solution for larger databases because of the additional overhead involved.
> Any assistance will be very appreciated.
> --
> Alex Ivascu
>
|||So... how do people handle connecting to a particular instance, since the port numbers are dynamic? Do they put a "list" of ports to try?
Thanks, Don.
Alex Ivascu
|||Now I think I know what you are talking about. In the Client Network
Utility in the Alias setup, if the "Dynamically Determine Port" box is
checked there is no additional setup needed on your servers.
That setting just means that the client will query the instance to determine
which port should be used and then use it.
HTH
"Alex Ivascu" <anonymous@.discussions.microsoft.com> wrote in message
news:D3956B77-CC1F-47D6-95A5-0F7595BA76D2@.microsoft.com...
> So... how do people handle connecting to a particular instance, since the
port numbers are dynamic? Do they put a "list" of ports to try?
> Thanks, Don.
> Alex Ivascu
>

dynamic port assignment

We have a customer who uses the dynamic port assignment option when a connection is established, instead of the default 1433. However, our application requires a fixed port number, either the default 1433 or an explicitly set port number
For now we are accessing this small database via the ODBC connector using the Microsoft-supplied ODBC for SQLServer driver. However, this is not a solution for larger databases because of the additional overhead involved
Any assistance will be very appreciated
-
Alex IvascYou can have SQL Server listen on multiple ports. So if the application
chooses between a list of ports you could simply have SQL listen on all of
them. Of course that is not the optimum solution for security reasons and
your firewall folks will probably not like the idea at all...
To configure SQL to listen on multiple ports; open up the Server Network
Utility, open up the TCP/IP properties and in the ports textbox enter in
your ports separated by commas.
"alexdivascu@.hotmail.com" <anonymous@.discussions.microsoft.com> wrote in
message news:8847E4B5-8BC9-4725-86F8-976AF93F945E@.microsoft.com...
> We have a customer who uses the dynamic port assignment option when a
connection is established, instead of the default 1433. However, our
application requires a fixed port number, either the default 1433 or an
explicitly set port number.
> For now we are accessing this small database via the ODBC connector using
the Microsoft-supplied ODBC for SQLServer driver. However, this is not a
solution for larger databases because of the additional overhead involved.
> Any assistance will be very appreciated.
> --
> Alex Ivascu
>|||So... how do people handle connecting to a particular instance, since the port numbers are dynamic? Do they put a "list" of ports to try
Thanks, Don.
Alex Ivasc|||Now I think I know what you are talking about. In the Client Network
Utility in the Alias setup, if the "Dynamically Determine Port" box is
checked there is no additional setup needed on your servers.
That setting just means that the client will query the instance to determine
which port should be used and then use it.
HTH
"Alex Ivascu" <anonymous@.discussions.microsoft.com> wrote in message
news:D3956B77-CC1F-47D6-95A5-0F7595BA76D2@.microsoft.com...
> So... how do people handle connecting to a particular instance, since the
port numbers are dynamic? Do they put a "list" of ports to try?
> Thanks, Don.
> Alex Ivascu
>

Dynamic Linking - Crystal XI - Image doesnt change

Im using desktop Crystal XI - connecting to a SQL database via ODBC connection.

Here is the problem: I would like to display a different excel file with each record in the report using dynamic linking.

Each record has a related document (xls) that is stored on a different server than the database. I have access to the folder on the server that contains all of the documents (I connect to this server by mapping a drive via windows explorer). I can manually add a document from the folder but the document doesnt change for each record, it just repeats the document that was added manually.

Ive created a formula field (based on records in the SQL db) to generate the link/document location:

"E:\LCM-" + cstr({mwebDocument.Doc_Ent_ID}, 0, "") + "\" + ({mwebDocument.Doc_File_Name}) + "_" + cstr({mwebDocument.Doc_ID}, 0, "") + "_1.xls"

(example return: E:\LCM-289\KC 2554 Testing Backup NOPAs_9609_1.xls)

I then reference the formula field in the graphic location.

So I know I can access the server/files since I can manually add a file.
I know that the file does display because the manually added file displays for each record (even though it doesnt change).
I know that the link is changing for each record because Ive made it a separate field and can verify its correctness.

Can anyone think of a reason why the object/file is not changing for each record?

Appreciate the help.Why don't u use the formula directly in detail section as a hyperlink..|||Thanks for the response - The major reason for not using a hyperlink is that we would like to distribute this report to people outside of our company (who wouldn't have access to the server/files). This was the initial reason to display the file in the report.

Dynamic FTP Connection

Hello ,

I have a table having different ftp url,user name, passwod, port no.I want to copy the file from all the location on my server at.

How can I change the connection string/ FTP location for FTP connection manager at rum time in SSIS.

Thanks

Hi,

You should be able to set up a variable and set its value to the Connection expression in the FTP Task.

If you right click on the FTP task and then select edit. The left hand menu should show a link for expressions. Add a new expression for Connection and set it to the variable you have set up to store this value.

This variable can be changed at runtime using Package Configurations.

Does this help at all.

Grant|||

Hi,

Can you please explain this in brief. As I am new to SSIS thats why facing problem in doing that. Should I add all the varibles such as

Remote Path, ServerUserName,serverPassword,filename,port. and how at run time these would change.

Thanks

|||

The way I do it is in a Script Task.

I first use a SQL Task to load the values from a table into variables I defined. Then I use those variables in code.

Here is my code:

PublicSub Main()

Dim ftpConn As ConnectionManager

ftpConn = Dts.Connections("ftp")

ftpConn.Properties("ServerUserName").SetValue(ftpConn, Dts.Variables("User_NM_FTP").Value)

ftpConn.Properties("ServerPassword").SetValue(ftpConn, Dts.Variables("Password_FTP").Value)

Dts.TaskResult = Dts.Results.Success

EndSub

|||

Al C. wrote:

The way I do it is in a Script Task.

I first use a SQL Task to load the values from a table into variables I defined. Then I use those variables in code.

Here is my code:

PublicSub Main()

Dim ftpConn As ConnectionManager

ftpConn = Dts.Connections("ftp")

ftpConn.Properties("ServerUserName").SetValue(ftpConn, Dts.Variables("User_NM_FTP").Value)

ftpConn.Properties("ServerPassword").SetValue(ftpConn, Dts.Variables("Password_FTP").Value)

Dts.TaskResult = Dts.Results.Success

EndSub

I have tried this, but I have many values in the table.I think this would work only for the first or last row.And how can I load the values in the variable through execute sql task.I am getting error when I do so.

|||You can certainly do it this way.

As i mentioned you should be able to do this via pacakge configurations. If you look up this in BOL it should give you an idea as to how it will be of use.

What effectively happens is that you set up a package configuration as say an XML format, the wizard takes you through what you want to set up in the package configuration. The values here can be assigned directly to the properties of the ftp component. When deployed the SSIS package looks up the configuration file and uses the values in this. To change the values you would just have to edit the XML file. There are a number of ways to set up a package configuration.

Al.C' suggestion of setting the variables in a script task is also equally valid. These variables may also be accessed from the likes of a .Net application in a similar manner and set up as and when the package is executed.

I'm not always as articulate as i'd like to be when explaining things but i hope that this helps.

The below links also helped me greatly when looking at dynamic modification of SSIS packages:

http://blogs.conchango.com/jamiethomson/archive/2005/02/28/1085.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/03/01/1093.aspx

Cheers,

Grant|||

The reason I had to use the Script Task was because of the ServerPassword which I wasn't able to set using configuration/expressions. See Brian Knight's response to my post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=648421&SiteID=1

|||

Grant Swan wrote:

You can certainly do it this way.

As i mentioned you should be able to do this via pacakge configurations. If you look up this in BOL it should give you an idea as to how it will be of use.

What effectively happens is that you set up a package configuration as say an XML format, the wizard takes you through what you want to set up in the package configuration. The values here can be assigned directly to the properties of the ftp component. When deployed the SSIS package looks up the configuration file and uses the values in this. To change the values you would just have to edit the XML file. There are a number of ways to set up a package configuration.

Al.C' suggestion of setting the variables in a script task is also equally valid. These variables may also be accessed from the likes of a .Net application in a similar manner and set up as and when the package is executed.

I'm not always as articulate as i'd like to be when explaining things but i hope that this helps.

The below links also helped me greatly when looking at dynamic modification of SSIS packages:

http://blogs.conchango.com/jamiethomson/archive/2005/02/28/1085.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/03/01/1093.aspx

Cheers,

Grant

Hi,

I have tried both the way. My problem is that I will get all the details from the database such as , Server IP, Port, User Name , Password, URL,File Name, Form where I have to download the file and I would have such 20 -30 different location (FTP) from where I have to fetch the file and put on our server. How this can be done using script task how can I loop trough all the rows in the table and save the file at our server.

|||

Al C. wrote:

The reason I had to use the Script Task was because of the ServerPassword which I wasn't able to set using configuration/expressions. See Brian Knight's response to my post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=648421&SiteID=1

Hi,

I have tried both the way. My problem is that I will get all the details from the database such as , Server IP, Port, User Name , Password, URL,File Name, Form where I have to download the file and I would have such 20 -30 different location (FTP) from where I have to fetch the file and put on our server. How this can be done using script task how can I loop trough all the rows in the table and save the file at our server.

|||

You insert a SQL Task that SELECT's the 20-30 different FTP sites. In the General tab you specify the ResultSet as 'Full result set'. On the Result Set tab you map the Result Name (0) to an object-typed user variable that you create to hold the recordset. Then you connect that task to a FOR EACH LOOP container. In the FOR EACH LOOP container you specify it is an ADO Enumerator and on the collection tab you choose your variable in the 'ADO object source variable:' drop down list. You also create user variables for each field of the FTP location you need for each FTP connection. Then on the Variable Mappings tab map the columns from your ADO recordset to your variables. Then inside your FOR EACH Loop container you place your Script Task which then uses those variables to set the FTP connection and the next task (also within the loop container) is the FTP task. This way the Script Task and the FTP task are performed for each iteration of the FOR EACH Loop container as it loops through each FTP location.

There is an example here of using a Foreach ADO enumerator:
http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx

|||

Al C. wrote:

You insert a SQL Task that SELECT's the 20-30 different FTP sites. In the General tab you specify the ResultSet as 'Full result set'. On the Result Set tab you map the Result Name (0) to an object-typed user variable that you create to hold the recordset. Then you connect that task to a FOR EACH LOOP container. In the FOR EACH LOOP container you specify it is an ADO Enumerator and on the collection tab you choose your variable in the 'ADO object source variable:' drop down list. You also create user variables for each field of the FTP location you need for each FTP connection. Then on the Variable Mappings tab map the columns from your ADO recordset to your variables. Then inside your FOR EACH Loop container you place your Script Task which then uses those variables to set the FTP connection and the next task (also within the loop container) is the FTP task. This way the Script Task and the FTP task are performed for each iteration of the FOR EACH Loop container as it loops through each FTP location.

There is an example here of using a Foreach ADO enumerator:
http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx

hi,

The above code worked for the iteration. Thanks a Lot for that. Now the problem is that How can a FTP connection Manager be configure dynamically in the script task. I have tried it but it fetches the file from only that location which I have given to configure it.

|||

Have you set a breakpoint in the Script Task to verify that the FTP Connection Manager properties are being set to the correct values for each iteration of the loop? Once you have dynamically configurated the ftp connection manager in the Script task then the FTP Task itself should have its IsRemotePathVariable set to True and then set the RemoteVariable to the variable which is mapped during the loop.

|||

Hi,

Thanks a lot Grant Swan ,Al C. I have the problem solved. This blog

http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx

helped a lot to complete the task.

|||Not sure if anyone will see this, but the disconnect I'm having on this problem is between the script task and the FTP task. The script task is getting data from my DB, but I don't know how to pass that into the FTP task. The package level configurations don't make sense, because the values need to be populated differently each time a ForEach Container scrolls through the recordset. It looks like the "expression" in the FTP task is the answer. But I don't know how to tell it to read the connection object from the script task. Maybe I need to pass it to a variable? Don't know how to do that either. Any help is greatly appreciated.

Dynamic FTP Connection

Hello ,

I have a table having different ftp url,user name, passwod, port no.I want to copy the file from all the location on my server at.

How can I change the connection string/ FTP location for FTP connection manager at rum time in SSIS.

Thanks

Hi,

You should be able to set up a variable and set its value to the Connection expression in the FTP Task.

If you right click on the FTP task and then select edit. The left hand menu should show a link for expressions. Add a new expression for Connection and set it to the variable you have set up to store this value.

This variable can be changed at runtime using Package Configurations.

Does this help at all.

Grant|||

Hi,

Can you please explain this in brief. As I am new to SSIS thats why facing problem in doing that. Should I add all the varibles such as

Remote Path, ServerUserName,serverPassword,filename,port. and how at run time these would change.

Thanks

|||

The way I do it is in a Script Task.

I first use a SQL Task to load the values from a table into variables I defined. Then I use those variables in code.

Here is my code:

PublicSub Main()

Dim ftpConn As ConnectionManager

ftpConn = Dts.Connections("ftp")

ftpConn.Properties("ServerUserName").SetValue(ftpConn, Dts.Variables("User_NM_FTP").Value)

ftpConn.Properties("ServerPassword").SetValue(ftpConn, Dts.Variables("Password_FTP").Value)

Dts.TaskResult = Dts.Results.Success

EndSub

|||

Al C. wrote:

The way I do it is in a Script Task.

I first use a SQL Task to load the values from a table into variables I defined. Then I use those variables in code.

Here is my code:

PublicSub Main()

Dim ftpConn As ConnectionManager

ftpConn = Dts.Connections("ftp")

ftpConn.Properties("ServerUserName").SetValue(ftpConn, Dts.Variables("User_NM_FTP").Value)

ftpConn.Properties("ServerPassword").SetValue(ftpConn, Dts.Variables("Password_FTP").Value)

Dts.TaskResult = Dts.Results.Success

EndSub

I have tried this, but I have many values in the table.I think this would work only for the first or last row.And how can I load the values in the variable through execute sql task.I am getting error when I do so.

|||You can certainly do it this way.

As i mentioned you should be able to do this via pacakge configurations. If you look up this in BOL it should give you an idea as to how it will be of use.

What effectively happens is that you set up a package configuration as say an XML format, the wizard takes you through what you want to set up in the package configuration. The values here can be assigned directly to the properties of the ftp component. When deployed the SSIS package looks up the configuration file and uses the values in this. To change the values you would just have to edit the XML file. There are a number of ways to set up a package configuration.

Al.C' suggestion of setting the variables in a script task is also equally valid. These variables may also be accessed from the likes of a .Net application in a similar manner and set up as and when the package is executed.

I'm not always as articulate as i'd like to be when explaining things but i hope that this helps.

The below links also helped me greatly when looking at dynamic modification of SSIS packages:

http://blogs.conchango.com/jamiethomson/archive/2005/02/28/1085.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/03/01/1093.aspx

Cheers,

Grant|||

The reason I had to use the Script Task was because of the ServerPassword which I wasn't able to set using configuration/expressions. See Brian Knight's response to my post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=648421&SiteID=1

|||

Grant Swan wrote:

You can certainly do it this way.

As i mentioned you should be able to do this via pacakge configurations. If you look up this in BOL it should give you an idea as to how it will be of use.

What effectively happens is that you set up a package configuration as say an XML format, the wizard takes you through what you want to set up in the package configuration. The values here can be assigned directly to the properties of the ftp component. When deployed the SSIS package looks up the configuration file and uses the values in this. To change the values you would just have to edit the XML file. There are a number of ways to set up a package configuration.

Al.C' suggestion of setting the variables in a script task is also equally valid. These variables may also be accessed from the likes of a .Net application in a similar manner and set up as and when the package is executed.

I'm not always as articulate as i'd like to be when explaining things but i hope that this helps.

The below links also helped me greatly when looking at dynamic modification of SSIS packages:

http://blogs.conchango.com/jamiethomson/archive/2005/02/28/1085.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/03/01/1093.aspx

Cheers,

Grant

Hi,

I have tried both the way. My problem is that I will get all the details from the database such as , Server IP, Port, User Name , Password, URL,File Name, Form where I have to download the file and I would have such 20 -30 different location (FTP) from where I have to fetch the file and put on our server. How this can be done using script task how can I loop trough all the rows in the table and save the file at our server.

|||

Al C. wrote:

The reason I had to use the Script Task was because of the ServerPassword which I wasn't able to set using configuration/expressions. See Brian Knight's response to my post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=648421&SiteID=1

Hi,

I have tried both the way. My problem is that I will get all the details from the database such as , Server IP, Port, User Name , Password, URL,File Name, Form where I have to download the file and I would have such 20 -30 different location (FTP) from where I have to fetch the file and put on our server. How this can be done using script task how can I loop trough all the rows in the table and save the file at our server.

|||

You insert a SQL Task that SELECT's the 20-30 different FTP sites. In the General tab you specify the ResultSet as 'Full result set'. On the Result Set tab you map the Result Name (0) to an object-typed user variable that you create to hold the recordset. Then you connect that task to a FOR EACH LOOP container. In the FOR EACH LOOP container you specify it is an ADO Enumerator and on the collection tab you choose your variable in the 'ADO object source variable:' drop down list. You also create user variables for each field of the FTP location you need for each FTP connection. Then on the Variable Mappings tab map the columns from your ADO recordset to your variables. Then inside your FOR EACH Loop container you place your Script Task which then uses those variables to set the FTP connection and the next task (also within the loop container) is the FTP task. This way the Script Task and the FTP task are performed for each iteration of the FOR EACH Loop container as it loops through each FTP location.

There is an example here of using a Foreach ADO enumerator:
http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx

|||

Al C. wrote:

You insert a SQL Task that SELECT's the 20-30 different FTP sites. In the General tab you specify the ResultSet as 'Full result set'. On the Result Set tab you map the Result Name (0) to an object-typed user variable that you create to hold the recordset. Then you connect that task to a FOR EACH LOOP container. In the FOR EACH LOOP container you specify it is an ADO Enumerator and on the collection tab you choose your variable in the 'ADO object source variable:' drop down list. You also create user variables for each field of the FTP location you need for each FTP connection. Then on the Variable Mappings tab map the columns from your ADO recordset to your variables. Then inside your FOR EACH Loop container you place your Script Task which then uses those variables to set the FTP connection and the next task (also within the loop container) is the FTP task. This way the Script Task and the FTP task are performed for each iteration of the FOR EACH Loop container as it loops through each FTP location.

There is an example here of using a Foreach ADO enumerator:
http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx

hi,

The above code worked for the iteration. Thanks a Lot for that. Now the problem is that How can a FTP connection Manager be configure dynamically in the script task. I have tried it but it fetches the file from only that location which I have given to configure it.

|||

Have you set a breakpoint in the Script Task to verify that the FTP Connection Manager properties are being set to the correct values for each iteration of the loop? Once you have dynamically configurated the ftp connection manager in the Script task then the FTP Task itself should have its IsRemotePathVariable set to True and then set the RemoteVariable to the variable which is mapped during the loop.

|||

Hi,

Thanks a lot Grant Swan ,Al C. I have the problem solved. This blog

http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx

helped a lot to complete the task.

|||Not sure if anyone will see this, but the disconnect I'm having on this problem is between the script task and the FTP task. The script task is getting data from my DB, but I don't know how to pass that into the FTP task. The package level configurations don't make sense, because the values need to be populated differently each time a ForEach Container scrolls through the recordset. It looks like the "expression" in the FTP task is the answer. But I don't know how to tell it to read the connection object from the script task. Maybe I need to pass it to a variable? Don't know how to do that either. Any help is greatly appreciated.

Dynamic FTP Connection

Hello ,

I have a table having different ftp url,user name, passwod, port no.I want to copy the file from all the location on my server at.

How can I change the connection string/ FTP location for FTP connection manager at rum time in SSIS.

Thanks

Hi,

You should be able to set up a variable and set its value to the Connection expression in the FTP Task.

If you right click on the FTP task and then select edit. The left hand menu should show a link for expressions. Add a new expression for Connection and set it to the variable you have set up to store this value.

This variable can be changed at runtime using Package Configurations.

Does this help at all.

Grant|||

Hi,

Can you please explain this in brief. As I am new to SSIS thats why facing problem in doing that. Should I add all the varibles such as

Remote Path, ServerUserName,serverPassword,filename,port. and how at run time these would change.

Thanks

|||

The way I do it is in a Script Task.

I first use a SQL Task to load the values from a table into variables I defined. Then I use those variables in code.

Here is my code:

Public Sub Main()

Dim ftpConn As ConnectionManager

ftpConn = Dts.Connections("ftp")

ftpConn.Properties("ServerUserName").SetValue(ftpConn, Dts.Variables("User_NM_FTP").Value)

ftpConn.Properties("ServerPassword").SetValue(ftpConn, Dts.Variables("Password_FTP").Value)

Dts.TaskResult = Dts.Results.Success

End Sub

|||

Al C. wrote:

The way I do it is in a Script Task.

I first use a SQL Task to load the values from a table into variables I defined. Then I use those variables in code.

Here is my code:

Public Sub Main()

Dim ftpConn As ConnectionManager

ftpConn = Dts.Connections("ftp")

ftpConn.Properties("ServerUserName").SetValue(ftpConn, Dts.Variables("User_NM_FTP").Value)

ftpConn.Properties("ServerPassword").SetValue(ftpConn, Dts.Variables("Password_FTP").Value)

Dts.TaskResult = Dts.Results.Success

End Sub

I have tried this, but I have many values in the table.I think this would work only for the first or last row.And how can I load the values in the variable through execute sql task.I am getting error when I do so.

|||You can certainly do it this way.

As i mentioned you should be able to do this via pacakge configurations. If you look up this in BOL it should give you an idea as to how it will be of use.

What effectively happens is that you set up a package configuration as say an XML format, the wizard takes you through what you want to set up in the package configuration. The values here can be assigned directly to the properties of the ftp component. When deployed the SSIS package looks up the configuration file and uses the values in this. To change the values you would just have to edit the XML file. There are a number of ways to set up a package configuration.

Al.C' suggestion of setting the variables in a script task is also equally valid. These variables may also be accessed from the likes of a .Net application in a similar manner and set up as and when the package is executed.

I'm not always as articulate as i'd like to be when explaining things but i hope that this helps.

The below links also helped me greatly when looking at dynamic modification of SSIS packages:

http://blogs.conchango.com/jamiethomson/archive/2005/02/28/1085.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/03/01/1093.aspx

Cheers,

Grant|||

The reason I had to use the Script Task was because of the ServerPassword which I wasn't able to set using configuration/expressions. See Brian Knight's response to my post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=648421&SiteID=1

|||

Grant Swan wrote:

You can certainly do it this way.

As i mentioned you should be able to do this via pacakge configurations. If you look up this in BOL it should give you an idea as to how it will be of use.

What effectively happens is that you set up a package configuration as say an XML format, the wizard takes you through what you want to set up in the package configuration. The values here can be assigned directly to the properties of the ftp component. When deployed the SSIS package looks up the configuration file and uses the values in this. To change the values you would just have to edit the XML file. There are a number of ways to set up a package configuration.

Al.C' suggestion of setting the variables in a script task is also equally valid. These variables may also be accessed from the likes of a .Net application in a similar manner and set up as and when the package is executed.

I'm not always as articulate as i'd like to be when explaining things but i hope that this helps.

The below links also helped me greatly when looking at dynamic modification of SSIS packages:

http://blogs.conchango.com/jamiethomson/archive/2005/02/28/1085.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/03/01/1093.aspx

Cheers,

Grant

Hi,

I have tried both the way. My problem is that I will get all the details from the database such as , Server IP, Port, User Name , Password, URL,File Name, Form where I have to download the file and I would have such 20 -30 different location (FTP) from where I have to fetch the file and put on our server. How this can be done using script task how can I loop trough all the rows in the table and save the file at our server.

|||

Al C. wrote:

The reason I had to use the Script Task was because of the ServerPassword which I wasn't able to set using configuration/expressions. See Brian Knight's response to my post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=648421&SiteID=1

Hi,

I have tried both the way. My problem is that I will get all the details from the database such as , Server IP, Port, User Name , Password, URL,File Name, Form where I have to download the file and I would have such 20 -30 different location (FTP) from where I have to fetch the file and put on our server. How this can be done using script task how can I loop trough all the rows in the table and save the file at our server.

|||

You insert a SQL Task that SELECT's the 20-30 different FTP sites. In the General tab you specify the ResultSet as 'Full result set'. On the Result Set tab you map the Result Name (0) to an object-typed user variable that you create to hold the recordset. Then you connect that task to a FOR EACH LOOP container. In the FOR EACH LOOP container you specify it is an ADO Enumerator and on the collection tab you choose your variable in the 'ADO object source variable:' drop down list. You also create user variables for each field of the FTP location you need for each FTP connection. Then on the Variable Mappings tab map the columns from your ADO recordset to your variables. Then inside your FOR EACH Loop container you place your Script Task which then uses those variables to set the FTP connection and the next task (also within the loop container) is the FTP task. This way the Script Task and the FTP task are performed for each iteration of the FOR EACH Loop container as it loops through each FTP location.

There is an example here of using a Foreach ADO enumerator:
http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx

|||

Al C. wrote:

You insert a SQL Task that SELECT's the 20-30 different FTP sites. In the General tab you specify the ResultSet as 'Full result set'. On the Result Set tab you map the Result Name (0) to an object-typed user variable that you create to hold the recordset. Then you connect that task to a FOR EACH LOOP container. In the FOR EACH LOOP container you specify it is an ADO Enumerator and on the collection tab you choose your variable in the 'ADO object source variable:' drop down list. You also create user variables for each field of the FTP location you need for each FTP connection. Then on the Variable Mappings tab map the columns from your ADO recordset to your variables. Then inside your FOR EACH Loop container you place your Script Task which then uses those variables to set the FTP connection and the next task (also within the loop container) is the FTP task. This way the Script Task and the FTP task are performed for each iteration of the FOR EACH Loop container as it loops through each FTP location.

There is an example here of using a Foreach ADO enumerator:
http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx

hi,

The above code worked for the iteration. Thanks a Lot for that. Now the problem is that How can a FTP connection Manager be configure dynamically in the script task. I have tried it but it fetches the file from only that location which I have given to configure it.

|||

Have you set a breakpoint in the Script Task to verify that the FTP Connection Manager properties are being set to the correct values for each iteration of the loop? Once you have dynamically configurated the ftp connection manager in the Script task then the FTP Task itself should have its IsRemotePathVariable set to True and then set the RemoteVariable to the variable which is mapped during the loop.

|||

Hi,

Thanks a lot Grant Swan ,Al C. I have the problem solved. This blog

http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx

helped a lot to complete the task.

|||Not sure if anyone will see this, but the disconnect I'm having on this problem is between the script task and the FTP task. The script task is getting data from my DB, but I don't know how to pass that into the FTP task. The package level configurations don't make sense, because the values need to be populated differently each time a ForEach Container scrolls through the recordset. It looks like the "expression" in the FTP task is the answer. But I don't know how to tell it to read the connection object from the script task. Maybe I need to pass it to a variable? Don't know how to do that either. Any help is greatly appreciated.

Dynamic datasource name

Hi
Is there a way we can define the datasource name/connection string for a
report in a config file... and read it from there so that it would make the
job of deploying across various servers easy?Various approaches for dynamic database connections in RS 2000 are
available:
* Use a custom data processing extension
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_5c2q.asp
* Use the SOAP API by calling SetDataSourceContents:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_2ojd.asp
* Use the linked server functionality of SQL Server - you should some
threads about it in the archives of this newsgroup.
* If the databases are on the same server, use a dynamic query text (i.e.
="select * from " & Parameters!DatabaseName.Value & "..table")
* If you're just toggling between two or three databases, you can publish
the same report 3 times with 3 different names using 3 different data
sources and write a main report that shows/hides the correct subreport based
on whatever criteria you want.
In addition, native support (expression-based connection strings) is
available in RS 2005: Finish the design of the datasets with a constant
connection string and make sure everything works. Then, go back to the data
tab and open the dataset/data source dialog and change the connection string
to be an expression. Use string concatenation to plug in the parameter
value. Here is an example of how the RDL would look for a parameter-based
connection string:
<DataSources>
<DataSource Name="Northwind">
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>="data source=" & Parameters!ServerName.Value
& ";initial catalog=Northwind;"</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<ReportParameters>
<ReportParameter Name="ServerName">
<DataType>String</DataType>
<Prompt>ServerName</Prompt>
</ReportParameter>
</ReportParameters>
You can also check this blog posting:
http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"PV" <PV@.discussions.microsoft.com> wrote in message
news:25C803F0-8944-4712-9A72-51F26DED394D@.microsoft.com...
> Hi
> Is there a way we can define the datasource name/connection string for a
> report in a config file... and read it from there so that it would make
> the
> job of deploying across various servers easy?

Dynamic database connectivity?

Dear SSIS experts,

I was wondering if there's such a thing that we could assign the database connection on the fly dynamically? so that I save the connection string or the database name somewhere and SSIS reads it on load time or something..

Thank you

Yes! Look into "package configurations." Examples and more information are in BOL and all over this forum.|||Thanks for ur quick reply; however, I can't find a thing! would u plz give me a link or something? Thanks |||Pull up Google and type in (without the quotes) "ssis package configurations"

The first few links should work very nicely for you.

The official page from Microsoft: http://msdn2.microsoft.com/en-us/library/ms141682.aspx

Dynamic Database Connection - EXCEL to SQL Server

Hi *, is there a way to connect Excel to SQL Server so that Excel serves as a frontend to SQL Server? I heard rumors that this is possible with the 2007 release.

I'm looking for a product that helps me storing massive data outside an Excel file. Right now, I'm using Palo (open source multidimensional database).

Regards,
Steve

Are you looking for something along the lines described in this article: http://support.microsoft.com/kb/321686 titled "How to import data from Excel to SQL Server"?

|||Well, something like that... Basically, I wanna use Excel as a Front-End to SQL Server. That means, data input AND output in Excel. Data storage in SQL Server.

Any ideas?|||

What about using ADODB.Connection in VBA. That way you can use Excel as your interface and store data in SQL Server.

Jakob

|||

Visual Studio Tools for Office focuses on the scenario that you describe...you can find a good article that walks through the same basic scenario at: http://msdn2.microsoft.com/en-us/library/aa192473(office.11).aspx

If you can't use Visual Studio Tools for Office, you could potentially use a poor man's version of what it does under the covers. By adding an "Update" column and using the code in the link that Peter provided earlier, you could determine what rows need to be updated and execute an appropriate query.

If you aren't bound to the unique functionalities of Excel, a linked server in Access might be an easier way to provide the basic reporting/editing functionality that you are looking for.

Dynamic Database Connection

Hi,

I done some Crystal reports(10.0) in asp .net using SQL server 2000(DB). While creating the reports, OLE DB Connection Information is must. Now i want to change the Source and Database for all the Reports. I want to do it Dynamically. Could I ?. I want to create reports without giving these(Source and Database) all. That is for each reports I dont want to give Source,Database,User Id and Password.
Pls help me in this regard.almost same problem with mine..
but mine is in report environment in vb6.

i'm not sure, but in vb6 (it think its not quite different :) )
u can do such this ..
firstly u must make a reference to the crystal report.
next...
--create a new Crystal Report object ...
it may looks like this

dim cr as new crystal report object

i think there must be a method of that CR object that could add a new report

hope this gonna help at least give a clue.. since i'm not that expert in .NET tech ..

good luck|||Hi szpilman,
The Problem is while creating the reports we have to give the Source and Database, But we should not do in this method. While creating the reports we should point one thing( It may be DSN).For this , All the reports should point out one (DSN(applies for VB .NET)). Here I want to point out all the reports to one( like DSN). Whenever I want to Change i should change in this Pointed one( like DSN ). Like DSN "i want one", where i can change the Source and Database dynamically.
Thank U

Dynamic connections rsInvalidDataSourceCredentialSetting

Hello,

Im having a few issues with a report this should use dynamic connections.

I have a connection based on an expresssion which uses a switch to evaluate a input and select the appropriate connection string for the item.

This all works fine when in BIDS it happily switches between servers no problems. when i then deploy the report to a SSRS server and try and view it i get the following error:

The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database. (rsInvalidDataSourceCredentialSetting)

The format of connection string i am using is :

Data Source=<<server>>;Initial Catalog=staging;uid=<<username>>;pwd=<<password>>"

I have checked the report properties on the SSRS server and the connection correctly reports the conection is expression based and no credentials are required.

as a trouble shooting exersize i tryed a report which you just pass the connection string into instead of using a switch and got the same error

I have also tried it on another server and get the same problem.

Any suggestions?

Thanks

Paul

Did you find an answer for this? I have the same problem.|||

No ive tried a number of different work arrounds but no luck

Just coming to terms with the fact i may have to have 10 reports instead of the 1

|||

Hello,

I had a similar issue with Dynamic connections. In the Report Manager, "No credentials are required" option was set. On searching I got to know that we need an unattended execution account in such cases.

To create an unattended execution account, take Reporting Services Configuration Utility -> Execution Account.

Add an existing account. Doing the above procedure solved my issue. Hope this helps. Let me know how it goes.

Regards,

Sonu.

|||

Added an execution account and the report runs like a dream!!

Cheers

Paul

Dynamic connections rsInvalidDataSourceCredentialSetting

Hello,

Im having a few issues with a report this should use dynamic connections.

I have a connection based on an expresssion which uses a switch to evaluate a input and select the appropriate connection string for the item.

This all works fine when in BIDS it happily switches between servers no problems. when i then deploy the report to a SSRS server and try and view it i get the following error:

The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database. (rsInvalidDataSourceCredentialSetting)

The format of connection string i am using is :

Data Source=<<server>>;Initial Catalog=staging;uid=<<username>>;pwd=<<password>>"

I have checked the report properties on the SSRS server and the connection correctly reports the conection is expression based and no credentials are required.

as a trouble shooting exersize i tryed a report which you just pass the connection string into instead of using a switch and got the same error

I have also tried it on another server and get the same problem.

Any suggestions?

Thanks

Paul

Did you find an answer for this? I have the same problem.|||

No ive tried a number of different work arrounds but no luck

Just coming to terms with the fact i may have to have 10 reports instead of the 1

|||

Hello,

I had a similar issue with Dynamic connections. In the Report Manager, "No credentials are required" option was set. On searching I got to know that we need an unattended execution account in such cases.

To create an unattended execution account, take Reporting Services Configuration Utility -> Execution Account.

Add an existing account. Doing the above procedure solved my issue. Hope this helps. Let me know how it goes.

Regards,

Sonu.

|||

Added an execution account and the report runs like a dream!!

Cheers

Paul

Dynamic connections rsInvalidDataSourceCredentialSetting

Hello,

Im having a few issues with a report this should use dynamic connections.

I have a connection based on an expresssion which uses a switch to evaluate a input and select the appropriate connection string for the item.

This all works fine when in BIDS it happily switches between servers no problems. when i then deploy the report to a SSRS server and try and view it i get the following error:

The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database. (rsInvalidDataSourceCredentialSetting)

The format of connection string i am using is :

Data Source=<<server>>;Initial Catalog=staging;uid=<<username>>;pwd=<<password>>"

I have checked the report properties on the SSRS server and the connection correctly reports the conection is expression based and no credentials are required.

as a trouble shooting exersize i tryed a report which you just pass the connection string into instead of using a switch and got the same error

I have also tried it on another server and get the same problem.

Any suggestions?

Thanks

Paul

Did you find an answer for this? I have the same problem.

|||

No ive tried a number of different work arrounds but no luck

Just coming to terms with the fact i may have to have 10 reports instead of the 1

|||

Hello,

I had a similar issue with Dynamic connections. In the Report Manager, "No credentials are required" option was set. On searching I got to know that we need an unattended execution account in such cases.

To create an unattended execution account, take Reporting Services Configuration Utility -> Execution Account.

Add an existing account. Doing the above procedure solved my issue. Hope this helps. Let me know how it goes.

Regards,

Sonu.

|||

Added an execution account and the report runs like a dream!!

Cheers

Paul

Dynamic connection to the Database

Hi,
I want to connect to several databases, is it possible to pass the
connection string as a parameter and change connection to the database
dynamically ?
Thanks.Try this
="data source=" &Parameters!ServerName.Value & ";initial
catalog=AdventureWorks
Of course you can change anything in the connection string... In your case,
you'd provide the catalog instead of the servername...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"yfat" wrote:
> Hi,
> I want to connect to several databases, is it possible to pass the
> connection string as a parameter and change connection to the database
> dynamically ?
> Thanks.|||I believe you want the SetDataSourceContents method. You can find an
explanation of it in BOL.
"yfat" wrote:
> Hi,
> I want to connect to several databases, is it possible to pass the
> connection string as a parameter and change connection to the database
> dynamically ?
> Thanks.|||Ok, I tried to create a dynamic data source with two parameters - the server
name and the database name like this :
="data source=" &Parameters!ServerName.Value & ";initial
catalog=" &Parameters!DBName.Value
but when I try to continue with that data source i get this error about
trying to connect the data base :
"A connection cannot be made to the database. Set and check the connection
string."
sice the connection is a dynamic one, I cannot know at the design time the
user and the password for the data base.
"Wayne Snyder" wrote:
> Try this
> ="data source=" &Parameters!ServerName.Value & ";initial
> catalog=AdventureWorks
>
> Of course you can change anything in the connection string... In your case,
> you'd provide the catalog instead of the servername...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "yfat" wrote:
> > Hi,
> > I want to connect to several databases, is it possible to pass the
> > connection string as a parameter and change connection to the database
> > dynamically ?
> >
> > Thanks.|||I just saw that the connection string on Reporting Services version 1.0
cannot be based on expressions, Is it possible on the next version ?
"yfat" wrote:
> Ok, I tried to create a dynamic data source with two parameters - the server
> name and the database name like this :
> ="data source=" &Parameters!ServerName.Value & ";initial
> catalog=" &Parameters!DBName.Value
> but when I try to continue with that data source i get this error about
> trying to connect the data base :
> "A connection cannot be made to the database. Set and check the connection
> string."
> sice the connection is a dynamic one, I cannot know at the design time the
> user and the password for the data base.
> "Wayne Snyder" wrote:
> > Try this
> >
> > ="data source=" &Parameters!ServerName.Value & ";initial
> > catalog=AdventureWorks
> >
> >
> > Of course you can change anything in the connection string... In your case,
> > you'd provide the catalog instead of the servername...
> > --
> > Wayne Snyder MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> >
> > I support the Professional Association for SQL Server ( PASS) and it''s
> > community of SQL Professionals.
> >
> >
> > "yfat" wrote:
> >
> > > Hi,
> > > I want to connect to several databases, is it possible to pass the
> > > connection string as a parameter and change connection to the database
> > > dynamically ?
> > >
> > > Thanks.

Dynamic connection to sql database via Access 2k7 project

I am using SQL server 2005 with multiple client databases with the identical
structure. Is it possible to disconnect from one database and connect to
another within the same ACCESS 2007 project? In Access 2007 connecting to
the database through an ODBC connection (with linked tables) is slower. I
would like to have the direct access for speed purposes. Any help on this
qould be greatly appreciated.
Refer to the following article:
How to programmatically change the connection of a Microsoft
Access project
http://support.microsoft.com/kb/306881
-Sue
On Tue, 5 Jun 2007 17:11:00 -0700, Ed C <Ed
C@.discussions.microsoft.com> wrote:

>I am using SQL server 2005 with multiple client databases with the identical
>structure. Is it possible to disconnect from one database and connect to
>another within the same ACCESS 2007 project? In Access 2007 connecting to
>the database through an ODBC connection (with linked tables) is slower. I
>would like to have the direct access for speed purposes. Any help on this
>qould be greatly appreciated.

Dynamic connection to sql database via Access 2k7 project

I am using SQL server 2005 with multiple client databases with the identical
structure. Is it possible to disconnect from one database and connect to
another within the same ACCESS 2007 project? In Access 2007 connecting to
the database through an ODBC connection (with linked tables) is slower. I
would like to have the direct access for speed purposes. Any help on this
qould be greatly appreciated.Refer to the following article:
How to programmatically change the connection of a Microsoft
Access project
http://support.microsoft.com/kb/306881
-Sue
On Tue, 5 Jun 2007 17:11:00 -0700, Ed C <Ed
C@.discussions.microsoft.com> wrote:

>I am using SQL server 2005 with multiple client databases with the identica
l
>structure. Is it possible to disconnect from one database and connect to
>another within the same ACCESS 2007 project? In Access 2007 connecting to
>the database through an ODBC connection (with linked tables) is slower. I
>would like to have the direct access for speed purposes. Any help on this
>qould be greatly appreciated.

Dynamic Connection Strings in SSIS

Possible or not? -->
I maybe lazy - but I want to achieve just specifiying 1 variable in SSIS package ("environment") - and all the connectionStrings should "poof" magically be adjusted to correct locations

In DTS I created a SetDTSenvironmentVariables function for all my packages - so how wouldIi achieve this in SSIS?

Function SetDTSenvironmentVariables( environment )
Folder = "MyDtsPackageFolder"
Select Case environment
case "DEV"
DTSGlobalVariables("WorkingDirectory").value = "C:\Packages" & Folder
case "STAGING"
DTSGlobalVariables("WorkingDirectory").value = "D:\Sql_working_directory\My_production\STAGING" & Folder
case "LIVE"
DTSGlobalVariables("WorkingDirectory").value = "D:\Sql_working_directory\My_production\" & Folder
End Select

'
' Set Connection Properties
'
dim oPackage, oConn
set oPackage = DTSGlobalVariables.parent
oPackage.LogFileName = DTSGlobalVariables("WorkingDirectory").value & "\Logs\Errors.txt"
For Each oConn In oPackage.connections
Select Case oConn.Name
case "My_DB"
Select Case environment
case "DEV"
oConn.datasource = "SERVER01"
oConn.Catalog = "My_Production"
case "STAGING"
oConn.datasource = "SERVER06"
oConn.Catalog = "My_Staging"
case "LIVE"
oConn.datasource = "SERVER06"
oConn.Catalog = "My_Production"
End Select
case "Schools.xls"
oConn.datasource = DTSGlobalVariables("WorkingDirectory").value & "\" & "School_Codes.xls"
case else
oConn.datasource = DTSGlobalVariables("WorkingDirectory").value & "\" & oConn.Name
End Select
Next

set oPackage = nothing
set oConn = nothing
End Function

The way I do this is to have a variable called RootFolder and all other directories are relative to that and hence can be set dynamically using an expression (on ConnectionString property of the appropriate connection manager).

RootFolder variable is set via a configuration. Its your choice as to what type of configuration you use.

I kinda talk about this a bit here:

Common folder structure
(http://blogs.conchango.com/jamiethomson/archive/2006/01/05/2559.aspx)

-Jamie

|||Awesome, dude

Thanks mate - only thing I wonder how do you get time to write all those blogs...|||

TheViewMaster wrote:

Awesome, dude

Thanks mate - only thing I wonder how do you get time to write all those blogs...

I wonder myself sometime.

I've been doing it for two years tho so there's quite a library of "stuff" up there now. I hardly ever write anything new these days.

-Jamie