SQL Server Integration Services also known as SSIS is a platform for data integration and workflow applications. The solutions being built include the three operations for data warehousing, which are Extract, Transform and Load (ETL). Integration Services include wizards and graphical tools for creating and debugging packages using a software called Visual Studio.

For an example, lets assume that we have a flat file(CSV format) which consists of data related to Inventory. Before creating the SSIS packages, it’s essential to analyse the data and fields available in the flat file to identify the dimension tables and the fact table.

Initially, we have to create the database in Microsoft SQL Server Management Studio. The steps are as follows :

  • Right Click on Database > New Database
  • In the New Database window, enter an appropriate Database Name > Click OK

Within the database that was created, we need to create the Staging Table, which will consist of all the required columns in the flat file, along with their appropriate data types. Steps to create a table are given below:

  • Right Click on Tables under the relevant database > New > Table
  • Enter the Column Name, Data Type and if the column Allow Nulls.

Based on the Flat File let’s assume that we have identified three dimensions such as :

  • Item
  • Type
  • Classification

Using the same steps mentioned to create the staging table, the tables for the three dimensions and the fact table need to be created. There after, we will create a database diagram for the tables that have been created. This can be done by :

  • Right Clicking on Database Diagrams > New Database Diagram
  • The window shown below will appear next. This is where you select the dimension tables and the fact table to create the relationships.
  • After adding the tables, the relationships among the tables need to be created. The unique column or columns can be specified as primary keys in the dimension tables. The specified primary keys will act as foreign keys in the fact table, therefore we need to create required relationships by matching the identical columns.

Now we can move on to working on Visual Studio. Firstly, a new project needs to be created. To create a new project :

  • Click On File > New > Project
  • In the new project dialog box, expand the Business Intelligence tab > go to Integration Services > Integration Services Project.
  • Next give an appropriate Name for the project and a different name as the Solution Name. In this context solution is a collection of projects.
  • Tick the Create directory for solution check box.
  • Click OK.

Before creating the packages it’s essential to create the required connections through the Connection Manager. In this scenario since our source file is a flat file, we need a file connection which allows a package to extract data from a flat file. Steps to add a flat file connection is as follows :

  • Right Click on Connection Managers > New Connection Manager
  • Select FLATFILE from the window shown below > Click Add
  • In the dialog box shown below click on Browse > Select the flat file
  • Tick the Column names in the first data row checkbox.
  • Click OK

After the flat file connection has been added, we need to add and configure an OLE DB Connection. The steps are as follows :

  • Right Click on Connection Managers > New Connection Manager
  • Select OLEDB from the window shown below > Click Add
  • In the dialog box shown below, Click New
  • Since we need to connect to SQL Server on the local PC we need to specify the Server Name as “localhost” or “.”
  • The user has the ability to Log on to the server using Windows Authentication or using SQL Server Authentication.
  • To connect to the database, under the Select or Enter A Database Name we need to specify the name of the database we created in Microsoft SQL Server Management Studio. In this case it would be the Inventory database.
  • Click Test Connection to make sure the settings being specified above are valid.
  • Click OK.

After all the required connections have been set up successfully, we can go ahead with creating the SSIS packages. The steps mentioned below can be followed in order to create a new SSIS package.

  • Right Click SSIS Packages > New SSIS Package
  • We can re-name the SSIS package according to our convenience.