Get files from different location using SSIS
Thanks for your reply ArthurZ, I have 100s of files. We use Leech process to FTP the file and unzip it. but now its need to replace the leech process and have SSIS does the decent job. there are different criteria for different files. (different location, username, password) To automate the process, so i dont need to create 100 different connections for 100 files, I'm following this method. For test purpose, I am using only 2 files. I've thaat information in table. I also figured out, how to unzip it in SSIS with the help of erlier posts. but now, I need to find out how to create each connection, FTP the file in every connection, and save that locally. Please let me know if you require any more information. Thanks,
January 6th, 2011 1:56am

I offer you to go with an Execute SQL Task like here http://www.sqlis.com/sqlis/post/The-Execute-SQL-Task.aspx then make a ForEach loop to go through the rows obtained from the config table (that are in the object type of a variable) set to Multiple Rows in the Execute SQL Task's Result set portion. The row from the config table gets parsed and package variables DataFeedLocation, UserName, Password, FolderName, FileName get populated using a Script Task. And finally, inside this ForEach loop you use the FTP task with an expression consuming the aforementioned variables to get the needed files.Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
January 6th, 2011 2:18am

As you said to assign table data to variables, I've used Data Flow Task as OLE DB as Source and Record Set as Destination. After assiging the columns to the variables from data set, the next step is FTP Task. I have different file locations on different feed locations. Like aftp.abcd.com aftp.xyz.com Is that doable to get different FTP Server name as variable? Also next question after this step will be - After connecting to the FTP Server, in FTP Task Editor, we can see all the folder structure of the FTP Server. In my requirement, I may have some files from the same location with different folder name and different file name. My guess is to assign file name with folder (Like /1123/ancd.txt.gz from 1123 folder name and ancd.txt.gz file name) Again, thanks for all your help
January 6th, 2011 2:48am

I apologize for asking small questions. 1) Execute SQL Task should be outside the ForEach loop. Is that correct? 2) Multiple row (Full Result Set) from Result Set option in Execute SQL Task should be selected. 3) Assign the values of column through Script Task (This step 3 should be inside the ForEach loop) Issue/Note: (Can step 3 can be done through Data Flow Task and assign result set to the record set?)
Free Windows Admin Tool Kit Click here and download it now
January 6th, 2011 2:49am

Re #1 - yes, it must be executed once to just get the recordset from the config table. Yes to #2 and #3. There is no data flow in step #3.Arthur My Blog
January 6th, 2011 2:55am

Hi all, Following the Arthur methodology: 1. Put a ExecuteSQL outside any container to retrieve the config table stored SQL, the result set must be assign to a object variable (that will be a recordset) 2. Put a For Each cotainer to loop over the previous point variable, you can loop the recordset using Foreach ADO Enumerator. On each loop take a row that you should assign to your varaibles: DataFeedLocation, UserName, Password, FolderName, FileName. 3. Add the FTP Task inside Foreach and configure the properties with expression taking your variables values. And run! Regards.Vctor M. Snchez Garca (ES) (BI) Hope this help. Please vote if you find this posting was helpful. if this is an answer to your question, please mark it. http://bifase.blogspot.com | http://twitter.com/atharky
Free Windows Admin Tool Kit Click here and download it now
January 6th, 2011 2:56am

I am referring to the question I had asked http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/ee6dc412-342f-4afe-a7de-44d7ca75417c I apologize that I had to go away from this project for couple of weeks. Now I'm back to this project. I need some of your help. I have created File System Task which deletes the existing files, FTP Task which gets file from FTP and Execute Process task which unzips the zip files. Thanks to Reza Raad, Shahrihar Nikkah. Now I also created a table with FTP Details which includes, DataFeedLocation, UserName, Password, FolderName, FileName. I'm stuck at this point. for many of the files, the DataFeedLocation is same. I created this table to get information as said in the previous post. What should be the next steps? Thanks a lot in advance
January 6th, 2011 3:22am

This looks to me a config table. Did you stuck because you do not know hot get the config values from this table and use them in your package?Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
January 6th, 2011 3:51am

I'm still stuck in Creating Dynamic FTP Connection Manager from the table dataset. Please provide some suggestions.
January 7th, 2011 8:35pm

I was told to try SSIS Configuration. Is that doable with SSIS Package Configuration?
Free Windows Admin Tool Kit Click here and download it now
January 7th, 2011 9:06pm

You should be looking into expressions, not configuraitons. Expressions are evaluated at the time that the property is accessed (i.e. changes during execution of package) while (using a simplified explanation of) configurations are loaded once as the package is loaded. You would assign the value of the items such as user name, file location, etc to a set of variables in the for each loop that is parsing the records gathered from the execute sql task. You would, in your ftp task, use an expression on the various properties you need to change dynamically to set the value for these items based off of the variables which you just populated from the loop. To be honest, I don't remember the degree to which the stock FTP task allows you to set these items via expression. If it does not, you may have to roll your own FTP via a script task. Please mark answered posts. Thanks for your time.
January 7th, 2011 9:18pm

It looks like you will want to build the connection string for the ftp connection manager through an expression. Servername (or IP).Port.Username.Password See the following thread: FTP Connection Manager: Set FTP Password using a variablePlease mark answered posts. Thanks for your time.
Free Windows Admin Tool Kit Click here and download it now
January 15th, 2011 3:58am

It looks like you will want to build the connection string for the ftp connection manager through an expression. Servername (or IP).Port.Username.Password See the following thread: FTP Connection Manager: Set FTP Password using a variablePlease mark answered posts. Thanks for your time.
January 15th, 2011 3:58am

Thank you Eric for your reply. I tried the Expression to change the connection manager properties. but having some issues with it. can't figure out how to change the password for FTP? Do I need to use Package Configuration for that? I need some help plz
Free Windows Admin Tool Kit Click here and download it now
January 15th, 2011 4:14am

Thank you Eric for your reply. I tried the Expression to change the connection manager properties. but having some issues with it. can't figure out how to change the password for FTP? Do I need to use Package Configuration for that? I need some help plz
January 15th, 2011 4:14am

Using Script Public Sub Main() Dim ftpConnectionManager As ConnectionManager Dim varFileName As String 'Dim varFolderName As String varFileName = "/" + Dts.Variables("User::FolderName").Value Dts.Variables("User::FileName").Value = varFileName ftpConnectionManager = Dts.Connections( "FTP Connection Manager" ) Dts.Connections( "FTP Connection Manager" ).Properties( "ServerName" ).SetValue(ftpConnectionManager, Dts.Variables( "FtpServer" ).Value) Dts.Connections( "FTP Connection Manager" ).Properties( "ServerPort" ).SetValue(ftpConnectionManager, Dts.Variables( "FtpPort" ).Value) Dts.Connections( "FTP Connection Manager" ).Properties( "ServerUserName" ).SetValue(ftpConnectionManager, Dts.Variables( "FtpUser" ).Value) Dts.Connections( "FTP Connection Manager" ).Properties( "ServerPassword" ).SetValue(ftpConnectionManager, Dts.Variables( "FtpPassword" ).Value) Dts.TaskResult = Dts.Results.Success End Sub Thanks, Cool Mind -- If you find my answer helpful, please mark it as Answer.
Free Windows Admin Tool Kit Click here and download it now
January 15th, 2011 5:26am

Using Script Public Sub Main() Dim ftpConnectionManager As ConnectionManager Dim varFileName As String 'Dim varFolderName As String varFileName = "/" + Dts.Variables("User::FolderName").Value Dts.Variables("User::FileName").Value = varFileName ftpConnectionManager = Dts.Connections( "FTP Connection Manager" ) Dts.Connections( "FTP Connection Manager" ).Properties( "ServerName" ).SetValue(ftpConnectionManager, Dts.Variables( "FtpServer" ).Value) Dts.Connections( "FTP Connection Manager" ).Properties( "ServerPort" ).SetValue(ftpConnectionManager, Dts.Variables( "FtpPort" ).Value) Dts.Connections( "FTP Connection Manager" ).Properties( "ServerUserName" ).SetValue(ftpConnectionManager, Dts.Variables( "FtpUser" ).Value) Dts.Connections( "FTP Connection Manager" ).Properties( "ServerPassword" ).SetValue(ftpConnectionManager, Dts.Variables( "FtpPassword" ).Value) Dts.TaskResult = Dts.Results.Success End Sub Thanks, Cool Mind -- If you find my answer helpful, please mark it as Answer.
January 15th, 2011 5:26am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics