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.