I had a requirement of fetching data files (.txt) from client server using sftp and insert them in our own databases. Initially i thought it would be easy, i just have to configure FTP task in SSIS and get it going but later i realized that SFTP is not supported in SSIS.
A little googling / binging (i have started prefering bing for microsoft related searches and also because i love bing wall papers, they are very appealing and informative) took me to www.winscp.com
Its a free utility sftp software and can be used through SSIS
Let me explain how it was done.
1. drag and drop execute process task from the Toolbox – > control flow item
2. drag and drop for each loop container from the Toolbox – > control flow item
3. drag and drop data flow task from the Toolbox – > control flow item and drop it inside for each loop container
4. connect Execute process task with for each loop container
5. you will have a picture something like this
6. Right click on for each loop container and click edit. Following picture will appear. Mention the folder on which to enumerate and define the type of files. (you can use *.* if you want to enumerate all types of files)
7. Click on the variable mappings and following screen will appear. Add a variable with the name str_FileName.
8. You are done with configuring for each loop container. Now move to Execute process task.
Right click execute process task and following screen will appear. Move to the process tab.
In the executable part, mention the location of WINSCP exe
In the arguments part, mention the credentials
(In my case, i have mentioned the FTP address saved in my WINSCP profile. its format is like this username@FtpUrl.com). After mentioning the credentials, mention the WINSCP scripting file location. so the whole value is Arguments section is
This scripting file contains the information to connect WINSCP and get the required files and save in our directory. I will show you the content of that file laters.
Now move to Working Directory portion and enter the file location where you want to keep the files that are obtained from the ftp
9. Now you are done with configuring Execute process task. Lets move on to Data flow task inside for each loop container and right click it and press edit.
It will take you to DATA FLOW tab.
Drag and Drop Flat File Source from the Data Flow sources (from toolbox) and drop Ole Db Destination from the data flow destination (from toolbox).
Link flat file and Old Db. You will get something like the below.
10. Right click on the Flat file source and click Edit. Enter flat file connection manager name (FileConn) and press ok.
11. At the bottom, in the connection managers portion, a FileConn icon will appear. Right Click it and press edit. In the advanced tab, enter the column names. When done, press okay.
It will open up the below screen.
12. Click On FileConn, right click it and press properties.
Go to the Expressions, click on the elipses as followed
13. After clicking, following image appears. click on the property combo, select connection string, after this, click on expression combo’s ellipse.
14. After clicking it, following image appears. It is expression builder. Drag the User::str_FileName to the Expression text box. This will map the User::str_FileName variable with the file name which is forwarded by the for each loop container. Thats why we define one variable at for each loop container and use it in the connection string property for FileConn to map them. Click OK
15. Now right click Ole Db icon and press edit. Make a connection to Database and provide the table name where data needs to be inserted. Make sure you map the column names correctly.
You would be seeing the below screen. Press okay.
16. You are good to go now. Run the package and it will work.
Last but not the least, the WINSCP script, that connects the SFTP client. It is like this. It is very much self explanatory so no need to explain.