Introduction To SSIS

ETL Process

ETL (Extract, Transform, and Load) is a process in data warehousing responsible for pulling data out of the source systems and placing it into a data warehouse.

ETL involves the following tasks.

• Extracting the data from source systems (SAP, ERP, and other operational systems), data from different source systems is converted into one consolidated data warehouse format, which is ready for transformation processing.

• Transforming the data may involve the following tasks

  Applying business rules (so-called derivations, e.g., calculating new measures and dimensions);

  Cleaning (e.g., mapping NULL to 0 or "Male" to "M" and "Female" to "F" etc.);

  Filtering (e.g., selecting only certain columns to load);

  Splitting a column into multiple columns and vice-versa;

  Joining together data from various sources (e.g., lookup, merge);

  Transposing rows and columns;

  Applying any simple or complex data validation (e.g., if the first 3 columns are empty, reject the row from processing).

• I am loading the data into a data warehouse, repository, or other reporting applications.

Implementation

Step 1

Click Start, point to All Programs, expand Microsoft SQL Server, and click SQL Server Data Tools.

On the File menu, point to New, and click Project.

Expand Business Intelligence in the "Installed Templates" pane and select "Integration Services." Name the project and click OK.

Step 2

Click on the Data Flow.

Drag and drop the OLE DB Source, Lookup, and OLE DB Destination from the SSIS Toolbox panel.

Step 3

Double-click on the OLE DB Source. The following screen will appear.

Make the new DB connection by clicking on the New button as we used to make while deploying the cube.

Specify the table's name or type the SQL query as source data.

Click on the Preview… button to see the data that will be transferred.

Click on the Columns option in the left side panel, select the desired columns, and click OK.

Step 4

Link the OLE DB Source component with the Lookup Component by dragging-dropping the blue arrow over the Lookup component.

Double-click the Lookup component and click the Connection option in the left panel. The following screen will appear.

What is Lookup?

Lookup is a Synchronous Transformation that allows you to perform an equi-join between values in the transformation input and in the reference dataset, similar to T-SQL. This transformation is used to join two datasets at a time. To join more than two datasets, we must put multiple Lookup transformations, similar to a T-SQL join condition.

Specify the table name or use the SQL query as we did in the OLE DB Source component.

Click on the Columns option in the left panel, map the columns as shown in the figure given below, and click OK.

Step 5

Join the blue arrow from the Lookup component to the OLE DB destination.

Double-click the OLE DB Destination component the following screen will appear.

Specify the destination database and the destination table.

Click on the Mappings option in the left panel, map the columns shown in the figure below, and click OK.

Step 6

Run the project.

Open SQL Server and view the data in the destination table. It must be populated with the desired data.