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.

SQL Server Import & Export Wizard

Sql server import and export wizard is one of the several Sql Server Integration Services (SSIS) building tools. It can be accessed from DTSWizard.exe, Sql Server Management Studio (SSMS) and Business Intelligence Devolopment Studio (BIDS).

Export data from sql server to an Excel file:

First we want to run the Sql Server Import & Export Wizard. To do that go to run and type DTSWizard.exe and press ok.Then it will open.

Click next. Then it will open another window.

In here specify the data source which the data come from. I chose SQL Server Native Client which is the sql server. Then choose what is the authentication and the database and click next. It will redirect to another window.

Then choose the destination which the data goes to. In here i chose the employeeExcel.xlsx file. Next choose excel version of the file. There is a check box called first row has column names. If we tick it then the first row displayed column names. Click next.

In this phase we have to choose one of two options. First one is 'copy data from one or more tables or views'. If we tick it we can directly select tables or views that data come from. Second one is 'Write a query to specify the data to transfer'. If we tick it we can write a sql query and extract data. First we look at first choice.

This screen displays the source and the destination we chose. Also we can specify the destination work sheet and we can map parameters by click 'Edit Mapping' button. We can also preview the extracted data by clicking the 'Preview' button.

In this phase we can tell run the package immediatly and we can save this package in a sql server or file system.

Here i save the package in my file system and i chose 'Do Not Save Sensitive Data' for package protection level because i dont want to save any sensitive data.

Then name the package and give the description. Specify the location to save the package and click next.

Then it will give us the report that what we have done. If it is ok then we can complete the process.

It will complete the process successfully and we can check whether the data copied successfully by opening the excel file.

Without choosing directly, what are the destination tables or views we can execute a sql query.

Choose 'Write a Query To Specify The Data To Transfer'.

And write a query as u wish. We can select brows a sql file that already wrote also by clicking the Brows button. Here i select the only name and age columns from Employee table. I don't want id column here. After write the query check whether the query is valid by pressing the Parse button.

The sql query is valid.

Then promt to the window that we can select the destination sheet. After specify a sheet name pressEdit Mapping button to map the data.

Here i got woning. It told there is a problem with age column type. We don't want to worry about it. Put the tick to convert and we can proceed.

The execution complete successfully.

The data will be successfully added to the excel file without id column.

In this post i demonstrated how to export data from the sql server to an excel file. Import data process also similar to this. We can give excel file, flat file or any other data source and sql server as the destination. Then like this we can import data to the database.

Data Integration & ETL

Effectively capturing, managing and using row data to originate meaningful Business Intelligence (BI) is requirement for enterprises today. The data can be reside in different places with different formats. So successfully guide present and future decisions all level of management must have control over this data, which is a big challenge. To achieve this goal modern industry uses a process called ‘Data Integration’.

What is ETL?

ETL means Extract Transform and Load. Extracting data from outside source, transforming them to fit operational needs, loading them into the targeted destination. ETL is the core process of Data Integration. It is generally associated with data warehousing.

Extraction:

Sources:

* Relational Databases

* Excel files

* Flat files

* etc

Transformation:

In the transformation process applies a series of business rules or functions to the extracted data from the source to make the data for loading into the target destination.

Load:

Destinations:

* Data warehouses

* Excel files

* Flat files

* etc

After ETL process extracted data can be used to Reporting and Analysis.