Dynamic FTP Connection

• Kiran Gazz

  Valued Member

  Points: 60

  More actions

  August 28, 2006 at 6:54 am

  #116477

  Hello ,

               I wanted to know how to make a dynamic FTP Connection. My FTP Server will be having a FTP server Name , File path, FTP Server Name, FTP User Name, FTP Password. Our Database maintains different FTP servers for different clients.In DTS we used below code in activex script task to connect to the FTP Dynamically.

  Set objTask = objPackage.Tasks(cstr("DTSTask_DTSFTPTask_2")).CustomTask

   

   objTask.SourceLocation = 0

  objTask.sourcesite = DTSGlobalVariables("g_str_ftpservername").Value

   objTask.SourceUsername = DTSGlobalVariables("g_str_ftpusername").Value

   objTask.SourcePassword = DTSGlobalVariables("g_str_ftppassword").Value

  objTask.NonOverwritable =  False

  objTask.SourceFilename =  srcFiles

  objTask.destsite = DTSGlobalVariables("g_str_filelocation").Value

   

                  Now we are migrating our workflows to SSIS (SQL Server 2005). The problem is we are unable to find solution for dynamically connecting to FTP Server which maintains a password. 

   Can any body help please.

  Thanks,

  Kiran G

• August 31, 2006 at 8:00 am

  This was removed by the editor as SPAM

• Deepesh Verma

  SSC-Addicted

  Points: 404

  More actions

  September 2, 2006 at 8:21 am

  #658170

  Hi Kiran,

   

  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

  Regards,

  Deepesh Verma