I would like to share my experience about a situation, where I was given a task to export a file(say XML) from FTP location and get the data from this file populated into our database tables. Moreover, this task was required to execute everyday periodically.
I asked my mentor and he introduced me to the concept of SSIS. Lets explore the experience here.
SSIS makes things easier. SSIS (SQL Server Integration Services) is MS SQL Server component, used for variety of data migration tasks. With respect to our scenario, we will be using two very efficient features:
1. FTP Task
2. Script task
How to create a SSIS package?
Basic steps included here.
- Launch “SQL Server Business Intelligence development Studio” which is a component of SQL Server. Then go to File -> New -> Project and you will see the following dialog box:
- Select “Integration Services Project” as a template and name the Project per your convenience and press OK.
You will see the below window in front of you:
- In the “Toolbox” which comes under View -> Toolbox, you will find the real power(Features) of SSIS.
- As per our current need, we need to create a FTP Task and Script task as shown below:
Here “Download file from FTP” is a FTP task and the “Script task” is Script task. The green arrow shown here is the direction of flow of control.
First it will execute FTP task after that it will execute the Script task.
- Double click on FTP box or select editor from properties, You will find the FTP editor as shown below:
Here you will find few fields that needs to be filled for proper operation. All fields are self-explanatory and can be customized per our need.for ex: “RemotePath” will accept the file location and name in the FTP.
- Similarly for Script task, Double click on Script box or select editor from properties,see script task editor below.
Here also, there are few fields which are self-explanatory and can be customized per our needs for ex: “ScriptLanguage” to define the language,”Entry point” defines the method which begins the execution.
Here we have made use of VB.Net. Moreover, here we have defined some User defined variables which are required to pass information like DB Connection string etc.
The button “Design Script…” plays more important role in execution of Script task. In the Design Script window, we will be writing the code for Downloading the file from FTP and populate the Database tables with data present in the downloaded file.
Moreover, we can compress/decompress files while we download from FTP.
Please note that, there is no native task available for SFTP task. For achieving SFTP task, we need help from third-party tools.
That’s all in Package creation. Just press F5. You are ready to proceed with your package.