SQL Server Integration Services: How to ETL (Extract, Transform, Load)

As data may come from several sources into the data warehouse, there is often a need to import data from flat files into the system. These flat files may be text files or csv files. The question that arises is, how do we import the data into database. The easiest solution is to use SQL Server Integration Services that is shipped along with MS SQL server. In order to demonstrate the solution, I am using SQL Server 2008 here.

Problem Description

We have a list of customers in a text files. Every row, but the first one, describes the customer details such as customer’s:

1. FirstName
2. LastName
3. Address
4. PhoneNumber

These values are separated by commas for simplicity. However, you are free to choose any punctuation. It’s always advisable to keep column name also in the very first row so that they give some meaning to values separated by commas in the rows below.



Approach

SQL Server integration services (SSIS) facilitates transporting data from source to destination but it is much more than that. We will see that shortly. Destination can be any database, Excel file or flat files itself. However, that depends on the business needs.

I shall limit my approach to a set of tools for this article and will take them to a higher level gradually in future articles.

1. Firstly, create a SSIS project.
   
   
   
   
2. Name it and save it.
   
   
3. On the dataflow tab in the middle, drag and drop a flat file source.
   
   
   
   
   
   
4. Give flat file source a name and double click on it.
   
   
   
   
5. If necessary, give connection a name and click on New to create a flat file source connection.
   
   
6. Browse to the flat file on the disk.
   
   
   
   
7. Select the check box to consider the first row of the flat file for column names.
   
   
   
   
8. Click on the mapping in the left hand pane and change names in the output column if you wish to.
   
   

After performing the above steps, our job is to bring the data from our source flat file to our destination. Let’s say our destination is a table named “Customer” in a database in SQL Server. Before you drag a destination OLE DB on to data flow tab, make sure you have created a customer table in SQL Server. If not, do it using the script below. For the easiness, I have created an auto created Customer ID in the table. Feel free to have it in the flat file directly.