Getting Start to Create Simple SQL Server Integration Service Package SSIS ETL Process
This post is only for those who just started to learn SQL Server Integration Service (SSIS). SSIS is a ETL (Extract,Transform and Loading) process which a dynamic tools to move data from multiple sources to multiple destinations.All post are designed to help one to be a expert MS BI Developer. These post provide step by step guide by creating example packages with mostly used SSIS component.
As I mentioned SSIS package pull data from multiple sources and load into multiple destinations. So we need to tell the package from where we are going to pull data and where we are going to load data. For that we need to create connection manager which create a connection to the source and let the package knows from where data will be pulled and where data will be load. if we pull data from one source then we need to create one source connection and load to one destination then we need to load one destination connection. SSIS can pull data from different source like Flat file (txt, csv),Excel file, SQL database, Oracle Database etc. coming article will be discuss about all the source and connections. Let's start to create a simple SSIS Package
In design mode, we can see different type of tabs -Control Flow-Data flow-Parameter-Even handler Control flow- Control flow control the data flow in an order. A control flow consists with some Task and three containers
Mostly used Task :
1.Data Flow Task
2.Execute SQL Task
3.Execute Package Task
4.Expression Task
5.File System Task
6.Script Task
7.Send Mail Task etc.
Three containers :
For loop container
For each loop container
Sequence container
my objective is to walk through step by step to create packages using above Tasks and containers
Let's start to create a package using Data flow task which pull data from one source and load data to one source later post we will go through step by step to create packages using above Tasks and containers
Scenario- we have a CSV file with personal data and we need to load this data into a different folder.
we have a CSV file named “Personalinfo” in the local drive (location C:\Users\kna4j\Documents\Blog\Source ) .we will use this file as our source and also we have five folder named fol1 where we load same data
Source path- C:\Users\kna4j\Documents\Blog\Source
Pic-3
This CSV file has 10 person’s first name and last name. We are going to load the same data into five different folders
Destination path- C:\Users\kna4j\Documents\Blog\Destination\Fol1
Creating package to load this CSV file into Fol1 folder
Select Business Intelligence
Select Integration service project
Give a project name -SSIS Container
Define path where project will be saved- C:\Users\kna4j\Documents\Blog\Project
Pic-6
Now we are creating a source connection for csv file
Right click on the bottom pan of design mode (Connection manager) and click on “New Flat file Connection”
Flat file connection manager editor will open
Pic-7
Source name –“PersonalInfoCSVSource”
Source Path- C:\Users\kna4j\Documents\Blog\Source\personalInfo.csv
Pic-8
Click on column (left pan) to preview column and data
We can see the personal data with first name and last name as we see in the source folder
Click OK
Pic-9
When control flow tab(1) is selected drag data flow task from SSIS tool box(Left pane)(2)
Give this task a name “DFT-PersonalInfo load”(3)
Pic-10
Click on Data flow task. It will move to data flow task design view with blank design page.
Drag a flat file source from SSIS tool Box )Left Pane
Give a name “PersonalInfo CSV source
Click on Source task to edit source connection.Source editor will open
Pic-11
Select PersonalinfoCSVSource connection manager from drop down.
Pic-12
Click on column (left pane) to map external and output column.
Click OK
Pic-13
Drag a flat file destination task from SSIS Tool Box and connect from source to destination with green arrow
Click on destination task to edit destination Source. Destination source editor open
Pic-14
Click New (1)
Flat file format page open. choose Delimited format (2)
Click ok
Destination connection manager window will open
Pic-15
1.Give a connection name -Dest_folderConection(1)
2.Click Browse to define the destination path here we select the folder Fol1.(2)
3. Give the file name PersonalInfo1(3)
4. file type “CSV(4)
Click Mapping from left pane
Pic-16
Map input and output columns(Source columns and destination columns)
Click OK
if nothing wrong we can see the error (red cross) gone in design page(pic-13)
Pic-17
To execute package right click on the package then click on execute package. If package run successfully we can see green tick symbol as above
Go to the destination folder (C:\Users\kna4j\Documents\Blog\Destination\Fol1) we can see a new CSV file named personalinfo1 with same data as source data.
See How easy to create a SSIS Package. Next post we will create a package using Execute SQL Task
Comments