A Simple ETL With SSIS

In my second post of Data Integration i described a little bit about DTS which was used by Sql Server 2000. After SQL Server 2005 came, it introduced a new tool for data integration which is Sql Server Integration Services (SSIS). In this post i'm going to describe a simple example of ETL.

For work with SSIS, Sql Server used a tool called Business Intelligence Development Studio (BIDS). First we look at BIDS before we start.

We design our ETL in the Design View. Mainly there is two design views, Control Flow View and Data Flow View.

Lets see how to make an ETL:

Always we start form Control Flow View. Go to Control Flow View and in the toolbox drag and drop the 'Data Flow Item'. To specify the behavior of data we have to build Data Flow Item.

To build the data flow double click the Data Flow Item or right click it and select Edit. Then we get data flow view.

Now you can see the toolbox with Data Flow Items. Select the 'Flat File Source' Item from it and drag and drop it to the design.

Now double click it to configure.

Here you have to make a connection to the file you are going to extract data. To do so click the new button.
Then you get Flat File Connection Manager Editor. There you have to give the file path, and several other things.

You have to fill this according to your flat file.
This is my flat file. I separate columns by comma and rows by semicolon.

 Give a name for the connection manager and browse the file by click Browse button.

Then specify 'Text Qualifier'. According to my file it is a space. Tick the 'Column Names In The First Data Row' check box if your file has column names in the first row. Then go to the 'Columns' tab.

Here you want to specify the Row Delimiter and Column Delimiter. As my file it is semicolon and comma. Then click OK.

Then you can see the data by clicking the Preview button. Then if everything is fine click OK.
Now you have configured the Flat File Source. Then you have to select destination for save the data. Here i select 'OLE DB Destination'. Drag and Drop it to the design view. Now select the Flat File Source you can see it contain two arrows. We use green arrow to redirect data in successful process and red arrow to redirect data in failure process. Click the green arrow and then click the 'OLE DB Destination'.

Double click it to configure it.

Again you have to make connection called 'OLE DB Connection'. Click the new button. Then you will get 'Configure OLE DB Connection Manager' window.

Click new to make a new connection.

Then give the server name and the database name you want to save the extracted data.

Then go to 'Mapping' tab to map the data.

Here mappings are ok. Then click OK. Now we created the data flow. It is look like this.

But there is a red icon in OLE DB Destination Item. So there is a problem in it. This is happen wen extracted data are not in the correct format. We have to convert the data and give them to the OLE DB Destination.
To do that delete the arrow between Flat File Source and OLE DB Destination and drag and drop the 'Data Conversion Item' into the design view. Then connect the Flat File Source to the Data Conversion item and double click it.

Tick the name and class items. Change the data type them as Unicode String[DT_WSTR]. Then if you want you can change the output alias as well. Don't forget to set the length as the length in database table columns Then click OK.
Then connect the Data Conversion Item with PLE DB Destination with green arrow and double click the OLE DB Destination.

Then you have to change the mappings in it. Connect the Copy of name to the name and Copy of class to the class. Then click OK.
Now we done the ETL. Now run it. You can do it several ways. There is a green arrow icon in the top of the BIDS, click it or right click the package and select Execute Package.
Then you can see the data in the database.

Like wise you can build different ETLs using different toolbox items.