I had a requirement of fetching data files (.txt) from client server using SFTP and insert them in our own database. 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 searching took me to www.winscp.com.
It’s free utility SFTP software and can be used through SSIS
Let’s see how it can be 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
You will see 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 executable
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 email@example.com /script=C:\filegetter.txt
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 later.
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. Let’s 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 see:
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 manager, 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 ellipse 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. That’s 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.
Lastly, here is WINSCP script that connects the SFTP client. It is like this. It is very much self explanatory: