Introduction To SSIS

What is SSIS?

SSIS stands for SQL Server Integration Services. It is one of the services of SQL Server and was introduced for the first time in SQL Server 2005. It’s a powerful replacement for the legacy DTS (Data Transformation Services) that exists in SQL Server 2000.

Why we need SSIS?

The main uses of SSIS package are,

1. Merging Data from Heterogeneous Data Stores Populating Data
2. Warehouses and Data Marts Cleaning and Standardizing Data Building
3. Business Intelligence into a Data Transformation Process Automating
4. Administrative Functions and Data Loading

Comparison of SQL Server Editions for SSIS

Feature	Enterprise	BI	Standard	Web/Express
Import and Export Wizard	Yes	Yes	Yes	Yes
Basic Data Adapters	Yes	Yes	Yes	Yes
SSIS Designer	Yes	Yes	Yes
Basic Transformations	Yes	Yes	Yes
Advanced Data Adapters	Yes
Advanced Transformations	Yes

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, 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 multiple sources (e.g., lookup, merge);
• transposing rows and columns;
• applying any kind of simple or complex data validation (e.g., if the first 3 columns in a row are empty then reject the row from processing).

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

Implementation

Step 1

1. Click Start, point to All Programs, expand Microsoft SQL Server 2012, and click SQL Server Data Tools.
2. On the File menu, point to New, and click Project.
3. Expand Business Intelligence in the "Installed Templates" pane and select "Integration Services". Name the project and click OK.
   
   
   

Step 2

1. Click on the Data Flow.
   
   
   

2. From the SSIS Toolbox panel, drag and drop the OLE DB Source, Lookup, and OLE DB Destination.
   
   
   

Step 3

1. Double click on the OLE DB Source. The following screen will appear.
   
   
   
   
2. Make the new DB connection by clicking on the New… button as we used to make while deploying the cube.
3. Specify the name of the table or type the SQL query as a source data.
4. Click on the Preview… button to see the data that will be transferred.
5. Click on the Columns option in the left side panel, select the desired columns, and click OK.

Step 4

1. Link the OLE DB Source component with the Lookup Component by dragging-dropping the blue arrow over the Lookup component.
2. Double click the Lookup component and click on 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 values 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 need to put multiple Lookup transformations, similar to a T-SQL join condition.

3. Specify the table name or use the SQL query the same way we did in OLE DB Source component.
4. Click on the Columns option in the left panel, map the columns as shown in the figure given below, and click OK.
   
   
   

Step 5

1. Join the blue arrow from Lookup component to the OLE DB destination.
2. Double-click the OLE DB Destination component the following screen will appear.
   
   
   
   
3. Specify the destination database and the destination table.
4. Click on the Mappings option in the left panel, map the columns as shown in the figure below, and click OK.
   
   
   

Step 6

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