Introduction
In this blog, we will learn how to Download Latest File from FTP using SSIS Secure FTP Task, We also learn how to connect with FTP/SFTP and also see how to Get Latest File list in ADO.net DataTable variable and How to sort ADO.net DataTable and get and set latest FTP File Path in a variable using Script Task.
We also learn how to get and set variables and how to use it in SSIS Script Task and Secure FTP Task.
Prerequisites
Before we perform the steps listed in this article, you will need to make sure the following prerequisites are met:- SSIS designer installed. Sometimes it is referred to as BIDS or SSDT (download it from the Microsoft site).
- Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
- Make sure ZappySys SSIS PowerPack is installed (download it, if you haven't already).
- (Optional step). Read this article, if you are planning to deploy packages to a server and schedule their execution later.
Requirements
- First, you will need to have SSIS installed
- Also, make sure to have SSDT
- Make sure to have installed ZappySys SSIS PowerPack.
What is FTP, FTPS, SFTP, FTP/SSL?
Many people still have confusion over various protocols around FTP terminology. Most common terms you hear are as below.
- FTP
- Classic FTP
- Secure FTP
- SFTP
- FTPS
- FTP/SSL
- FTP over SSL
- FTP Over SSH
You must be overwhelmed by now? So are they all same or different? See below for quick clarification in nutshell.
Mainly there are 2 protocols for FTP and yes they are very different but still used to do Files Transfer to/from the remote server.
- Classic FTP – sometimes referred as just FTP (Typically server runs on Port 21)
- SFTP – Sometimes referred as Secure FTP or FTP over SSH (Typically server runs on Port 22)
So in short SFTP is not the same as the Classic FTP protocol. SFTP is more secure because traffic is always encrypted. On the other hand, Classic FTP is not encrypted by default but you can use FTP over SSL to request encrypted traffic in classic FTP. FTP over SSL also referred as FTPS or FTP/SSL.
Getting Started
In order to start, we will show several examples. ZappySys includes an SSIS – Secure FTP Task that will help you Download Latest File From FTP/SFTP, get file list in ADO.net DataTable variable and do many more actions using SSIS Script Task.
In this example, we will show 3 examples.
- First, we will show how to get FTP File List as ADO.net DataTable in a variable using SSIS – Secure FTP Task.
- Secondly, we will show how to sort ADO.net DataTable and get latest FTP File Path in a variable using Script Task.
- Third, we will Download Latest File from FTP using SSIS Secure FTP Task using that latest file path variable.
Get FTP File List as ADO.net DataTable in a variable using SSIS Secure FTP Task
Follow these steps to accomplish the task:
- Drag and drop Secure FTP Task the design panel and rename it as “Get File List as ADOnet DataTable” if you want to.
ADD SSIS – ZS SECURE FTP TASK
Connect with FTP/SFTP
Get Latest file list from FTP to ADO.net table in a variable
Set Latest ADO.net DataTable Variable
Get latest FTP File Path in a variable using Script Task
Follow these steps to accomplish the task:
1. Drag and drop SSIS Script Task the design panel and link it with the ZS SFTP Task.
Add SSIS – Script Task
2. Before proceeding with Script Task let’s add one new SSIS variables first that will store the Latest FTP File Path.
Add Latest File Variable
3. Now edit Script Task and add User::v_dtFileLists and User::v_LatestFilePath to ReadOnly/ReadWrite variable lists:
4. Then proceed and press “Edit Script” button to edit the code the Script Task — it will open the “ScriptMain.cs” C# file for you in new VS Project. And Write The Following Code in it.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
DataTable dt = (DataTable)Dts.Variables["User::v_dtFileLists"].Value; DataView dv = dt.DefaultView; dv.Sort = "CreationTime desc"; DataTable sortedDT = dv.ToTable(); if (sortedDT.Rows.Count > 0) { Dts.Variables["User::v_LatestFilePath"].Value = sortedDT.Rows[0][0].ToString(); } dt=null; dv = null; sortedDT = null; |
Get Latest File Path in Variable
5. Click Ok In “Script Task” And it will save and close the “ScriptMain.cs” Task.
6. That’s it we have successfully completed our second task. Let’s move on next step.
Download Latest File from FTP using the latest file path variable
Follow these steps to accomplish the task:
1. Drag and drop ZS Secure FTP Task the design panel and link it with Script Task.
Add SSIS – ZS Secure FTP Task
2. Let’s use that latest file variable and FTP Connection to download the latest file in the local folder. Follow the mentioned steps of the below screen.
Download Latest File using variable using SSIS -Secure FTP Task
3. Click OK to close Secure FTP TASK UI.
Finally, run the package and you will be able to see the latest file downloaded on your local folder from the FTP/SFTP.
Conclusion
To conclude, we can say that working with FTP/SFTP is now very simple. In this article, we looked at SSIS SFTP task examples on how to Download latest File from FTP using SSIS in the local folder. We also learned how to Sort ADO.net DataTable and Set latest file path in the variable using SSIS Script Task. We used variables, expressions, and SSIS Script Task. If you liked the tasks you can start using them by downloading SSIS PowerPack from our website here.
References
