OLAP

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.

Why SSIS?

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.

Create package:

  • 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:
SSIS-Project

SSIS-Project

 

  • 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:

SSIS-Window

SSIS-Window

 

  • In the “Toolbox” which comes under View -> Toolbox, you will find the real power(Features) of SSIS.

SSIS-ToolBox

  • As per our current need, we need to create a FTP Task and Script task as shown below:
SSIS-Flow

SSIS-Flow

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:
SSIS-FTP

SSIS-FTP

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.
SSIS-Script

SSIS-Script

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.

Author: jackie

I am a developer.

Share This Post On

0 Comments

  1. nice superb explaination

    Post a Reply
  2. thanks for the tutorial
    I am having a problem.
    The feature “Create SQL server database” isn’t available , I can’t select it.
    Can you help, please ?

    Post a Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

More from CodeSpread:

  • VB.Net: Download file from FTP and UnZipVB.Net: Download file from FTP and UnZipExperience: Many application requires data migration tasks to execute on daily basis. To achieve this we generally seek help of SSIS package, if at all we are making use of SQL sever. ...
  • C# Code to send a MailC# Code to send a MailWhile working in a project, we always neglect the importance of the code which we have written many times and now end up in copy pasting the code from old projects. Lately, I saw a request in fo...
  • C#: My experience with const keywordC#: My experience with const keywordThere are numerous post on internet about 'what is const keyword in .net' or 'what is the difference between const keyword and read only' and I must say many might have already mugged it up like me...
  • Concepts: S O L I DConcepts: S O L I DSOLID - Object oriented programming principles. May be its easy to forget these principles  but for creating a reusable code or maintainable code, people will always have to come back and check ...
  • Nullable .Net typesNullable .Net typesWe declare .net types in our project daily but still we do not recollect default values for each .net type so we end up doing null check for each data type. Its a basic mistake of a developer but ....
  • Useful ‘ref’ and ‘out’ parametersUseful ‘ref’ and ‘out’ parameters It took me some time to realize the real potential of ref and out keywords, till the time I experience a condition which could only be simplified by implementing ref and out keyword. Lets st...
  • Why Multiple Inheritance is discouraged?Why Multiple Inheritance is discouraged?Hi Guys, This is Sathish Again,Today we will discuss why most of the higher languages eliminates Multiple Inheritance? Most of you guys already know that multiple inheritance is not available for...
  • Asp.Net: More about CookiesAsp.Net: More about CookiesWe have seen, how our cookies looks in our last article Cookies Part 1. In this article, we will talk about their properties,limitations and technical part. Properties and Limitations Cook...