How to create a simple SSIS package

I would like to give the instructions from the very first step so anyone who reads the blog can understand how to do it. So to start SSIS you need to have SQL Server Business Intelligence Development Studio installed. First open Business Intelligence studio and to create the first project . Create a new project (new ->Project ) under Business Intelligence Project Select Integration Services Project give an appropriate name and click ok.

Creating A Connection Manager

What I recommend for the first step in creating SSIS package should be creating a connection manager. In the Connection Manager panel right click and click new OLEDB Connection (It can be any other depending on your need for example if you are planning to handle your operation using flat files you should select flat file connection. If its on log file it should be flat file connection). So here we are talking about DB connection. Click new and give the appropriate Connection and the DB name

Before Finishing you should always test the connection , in order to avoid failure when running the package

SSIS Introduction

SSIS (SQL Server 2005 Integration Services ) is the data transformation standard in SQL 2005 which replaces the DTS packages in SQL 200o.SSIS has a platform for building high performance data integration solutions, including extraction, transformation, and load (ETL) packages for data warehousing.

You can start doing SSIS using Business Intelligence Studio which provides a graphical tool kit for designing and debugging SSIS packages. It has more functionalities than DTS(in SQL 2000) and it can work parallel packages faster. I would like to explain how to create SSIS packages using this blog mainly because when I was learning there were not many resources to learn. So I would like to explain the functionalities of SSIS using graphics/Vedios in a simpler way

How To Create Control Flow Items

There are many control flow items in SSIS unlike in DTS.I am going to demonstrate few manly used control flow items such as Execute SQL task, Execute Package Task, Data Flow Task . I would like to explain other items in my next article.

Execute SQL Task – This is used to execute SQL statements like insertions and truncations.

To create SQL task drag and drop the SQL Task from the control flow item tab and double click the package.

Specify the task name and the description in properties window. Double click the SQL task and give the connection type it can be OLEDB/Excel, Then select the connection manager- which has the DB/Tables you are using to run the SQL statements.

Under SQL statement write the SQL statement and parse the query to test for the SQL syntax.

This is how it looks like in when you double click the SQL execution task,things which are marked in red are the things you should be careful when creating an execute SQL task

Execute Package Task-This is used to call other packages , drag and drop the execute package tasks and under general field give the proper name and description,

Under package give the connection and the relevant package u want (packages must be created previously).

When creating the package make sure to adjust the fail package on failure property true in properties tab so if the calling package fail it will fail the parent package as well.

How Build A Data Flow Task ( Transform data through SSIS )

You can transform data from one location(source) to another(destination) with adding modification to the data using Data Flow Tasks..To Create a Data Flow you need to select the Data Flow Task from the control flow panel and add to the control panel tab and double click on it . chose the appropriate Data Source and the Data Destination for the data transformation.

Data Source Can Be

* Data Reader Source – Extract data from relational DB by using .Net Provider

* Excel Source – Extract data from excel work book

* Flat File Source - Extract data from flat files

* OLE DB Source - – Extract data from relational DB by using OLE DB connection

Data Destination Can be

* Data Mining Model Training

* Data Reader Destination

* Dimension Processing

* Excel Destination

* Flat File Destination

* OLE DB Destination

I am going to discuss on OLE DB Source to OLE DB data transformation.

First drag and drop the OLE DB Source task and rename the name and description

Double Click the Task –

Select the OLE DB Connection Manager

Data Access Mode

Data Excrating Source – Which can be SQL command,Table/Veiw according to the data access mode you select

Always preview the table before closing the tab in order to avoid errors at the execution state

Then Drag and drop OLE DB Destination task and name it appropraiately

Before you do your mapping you should connect the Source and the destination first

For that you can just connect two task using OLE DB Source output(select both two packages right click -> add path-> OLE DB Source output)

Or

You can put any Data Flow Transformation task and then connect

Data Flow Transformation Tasks are show in the picture, you can use any of these task in-between and map to the destination

After mapping the source with the destination you can double click the Destination task and select the appropriate destination table from the connection manager and arrange the mapping accordingly

How To Deal With Slowly Changing Data In SSIS

In a data warehouse it is important to keep historical data as well as the new data.. In SSIS there is a way to get slowly changing data using Slowly changing dimension under data transformation task..It is a way of inserting data from source to destination with the Type 1 and Type 2 changes.

First you should create the source using a proper data source .Then drag and drop the slowly changing dimension task and give the specification as shown below

Connection Manager – Make a connection to the source and give that connection, it can be a database or an excel sheet.

Table or view – Specify the source table

Input Columns – Columns from the source

Dimension Columns – map the source columns with the destination columns

Key Type – Specify the primary key/Composite keys

And click next

Select a change type for slowly changing dimension columns specify the dimension columns sand the change type (in a data ware house all attributes should be changing attributes other than attributes like ( last updated date/date created) if you put any attribute as fixed attribute it will not updates the data when it comes for type 1 changes so I prefer not to use fixed attribute type if an attribute is not changing attribute then do not mention it in the changing attribute columns) click next

Inferred Dimension Members – enable inferred member support only if you think fact tables may reference dimensions members that are not yet loaded

(you should keep it as disable if you are not sure about it).. Click next

In the fixed and changing attribute option select change all the matching records, include out dated records, when changes are detected in changing attributes and finish slowly changing dimension wizard

It will automatically create the dimension with appropriate type 1 and type 2 fields where if an attribute is type1 it will updates the specific column if its type 2 it will insert as a new record.

You can rename the task accordingly using properties.

How To Create A Dynamic Log File In SSIS

In SSIS you can create a log file to track the behavior of the SSIS package execution. These are the steps used when creating a dynamic log file

• Create a log file in connection manager.

• Right click on connection manager ->New File Connection->give the proper sources to the log file.(Usage type- ,Existing file/New file Path- path of the log file)

• Or go to SSIS -Login (in the menu bar) and create a new File Connection

• Login Configurations

• In the menu bar Click SSIS -> Logging.

• Click checkbox for the Package(left side).

• Select the configuration and give a name for the log file.

• Click the Details tab to fire the events.

• Making the log file dynamic

• In the properties tab of the log file connection (click on log file in the connection manager and view the properties tab) Click on expression and give the following settings

• Property – Connection String

• Expression - @[User::gvErrorLogDir] + @[System::PackageName] + " "+ (DT_WSTR, 4) YEAR( GETDATE() ) + "-"+ ((LEN((DT_WSTR, 2) MONTH( GETDATE() ) )>1)?(DT_WSTR, 2) MONTH( GETDATE() ):"0" + (DT_WSTR, 2) MONTH( GETDATE() ) )+"-"+((LEN((DT_WSTR, 2) DAY( GETDATE() ) )>1)?(DT_WSTR, 2) DAY( GETDATE() ): "0"+ (DT_WSTR, 2) DAY( GETDATE() ) ) + " " + ((LEN((DT_WSTR, 2) DATEPART("Hh", GETDATE() ) )>1)?(DT_WSTR, 2) DATEPART( "Hh", GETDATE() ) : "0" +(DT_WSTR, 2) DATEPART( "Hh", GETDATE() ) ) + "h-"+ ((LEN((DT_WSTR, 2)DATEPART( "mi", GETDATE() ) )>1)?(DT_WSTR, 2) DATEPART( "mi", GETDATE() ) :"0" + (DT_WSTR, 2) DATEPART( "mi", GETDATE() ) ) + "File.txt"
  

• 
  

Relevant Links
How To Create A Logger In SSIS
SSIS Junkie
Integration Services Log Providers-MSDN

Winnie The Pooh And Friends

Winnie-The-Pooh

P-P-Piglet

Tigger-"That's T-I -Double Guh-er"

Eeyore

Owl

Raabit

Kanga

Roo