Hello All, We have process which gets files from different location and saves on our drive and unzips it. but its old process and requires to be rebuild. I am thinking to use SSIS for it. I have list of all file ftp location, username, password. and Desination location. what steps do i need to achieve this? Thanks in Advance

you can create a mapping data source for example a sql server table, or an excel file, or a .csv file , and put mapping data there, by Mapping Data I mean, each source data and destination data, for example you want to get ftp file from a location with username1 and password something to a destination local folder. you put all these information as a row in the mapping data source, then in a data flow task, you can read mapping data in an object type variable, and then use Foreach loop to loop through this object type variable with enumerator set as ado enumerator, you can fetch data of source ftp in variable mapping tab of foreach loop and also destination folder, then put a ftp task and set connection with expressions with source ftp variables. then put an execute process task to unzip downloaded file to selected destination. there are lots of details here, let me know where you need more information.http://www.rad.pasfu.com

to unzip files check http://www.sql-server-performance.com/faq/ssis_unzip_p1.aspx http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/00f110d9-9be8-481c-9696-75be9973e728 http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/DTS/Q_24853591.html http://www.pragmaticworks.com/Products/Business-Intelligence/TaskFactory/Features.aspx#TS4 To loop through folders check http://plexussql.blogspot.com/2009/10/how-to-loop-through-files-in-ssis.html to Load Txt or CSV files Check http://plexussql.blogspot.com/2010/06/looping-through-csv-files-using-ssis.html To lood Excel files check http://plexussql.blogspot.com/2010/04/looping-through-excel-files-and-sheets.html BUT you have to have the maaping sources to move the files from the Source (unziped) to a source folder of each ETL Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Thanks to all of you. My first step will be setting up the SSIS for individual connection. And I am still not sure where to start with. @Reza: Do I need to start with FTP Task? you were telling about Creating Mapping Data Source. Not sure about that. Have really small amount of SSIS knowledge Thanks,

where do you have information of source ftp and destination local paths? put all these in a mapping table or an excel file and use a data flow task to read this mapping table into a RecordSet Destination and write result to an object type variable. does these make sense to you?http://www.rad.pasfu.com

I like rezas suggestion, and i like to add that 1- make a SSIS package for each individual ETL ( get the packages working) 2- make a SSIS package that UNZIPS the zipped packages and copies them to the right source folder location 3- make a package that FTPS the source to the folder that is going to unzip files Once you have all these small packages you can combined them and make one or maybe few central package to handle the main steps. Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).