How to Create, Deploy and Configure SSIS Package: Part 1

Data loading from various source is a common task for data centric application. SSIS package is a very handy and easy to learn tools to load data from various source like excel, flat file or other database. In this SQL Server Integration Service(SSIS) series we try to learn, how to create, deploy, configure and monitor SSIS package. In first article of the series we will create a simple SSIS package which extract data from excel file then load the data to a SQL server database.

Initial Setup

Before we do anything else, we need to create necessary table to which we want to load data from the excel file. Lets create the.

CREATE TABLE DataLoadTest

(

       Id int IDENTITY(1,1) PRIMARY KEY,

       Name nvarchar(500) NULL,

       FirstColumn nvarchar(500) NULL,

       SecondColumn nvarchar(500) NULL,

       GroupOrder int NULL

)

GO

Create Package

To create a SSIS package right click the package folder of SSIS project and click 'New SSIS Package' option. It will create a package with default name 'Package1'. We rename our package to ExtractExcelData.dtsx.

Adding Necessary Connection Manager

Before we add other component in our package we need to add our required connection manager. In our package we need at least two connection manager. For reading data from excel file we need a excel connection manager and an oledb connection manager is required to load data to sql server database. Lets first add the oledb connection manager. To add right click on 'Connection Manager' folder of the project and click 'New Connection Manager'. It will open the connection manager window from where we can select our desired connection type. 

Add OLE DB Connection Manager

Select 'OLEDB' and click add button.

When the Configure OLE DB Connection Manager dialog box appears, click next and open Connection Manager dialog box. From the server name drop down choose SQL Server instance name where the destination database located. Then configure the authentication mode, we can use SQL server authentication as well as windows authentication. Select the database name from the drop down. To ensure the successful connection click Test Connection button.

Now close the dialog box by clicking OK button. Our first connection manager has been created. We rename our connection manager to ' DestinationDB.conmgr'. 

Add Excel Connection Manager

To add excel connection manager choose connection type Excel  from Add SSIS Connection Manager dialog box and click add button. It will launches Excel Connection Manager dialog box. Specify the full file path in the Excel File Path text box and close the dialog box by clicking OK button.

Adding Data Flow Task to the Package

 

So far we have created a empty package and two connection manager. Our goal is reading data from an excel file and load the data in our target table. To achieve this we need to add a data flow task in the control flow tab of our package. Drag a Data Flow Task from favorites section of the SSIS Toolbox and drop it to the Control Flow tab of the package.

After adding the data flow task double click the task to go to the Data Flow tab. In this tab we will do our remaining job.

Add Excel Source

 

To add Excel Source in the data flow tab drag excel source from other sources section of the SSIS Toolbox and drop it to the Data Flow tab. We can also rename the source and give it a meaningful name.

Double click the Excel Source to configure the source. It will open the Excel Source Editor dialog box. From this dialog box choose our added excel connection manager. In the Name of the Excel sheet drop down all existing sheets are available. We have to choose our desired sheet. Select Column tab of the Excel Source Editor dialog box. Here we can see all of the columns that exists in the excel sheet. Select the necessary column that we want to load. Click OK button and close the dialog box.

Add OLE DB Destination

To add OLE DB Destination drag it from SSIS toolbox and drop it to the Data Flow tab. Connect the Excel Source to the OLE DB Destination by joining the input arrow of the Excel Source to the OLE DB destination. Double clicking on OLE DB Destination will open OLE DB Destination Editor dialog box. From this dialog box we can choose destination connection manager, destination table and we can also specify the column name that we want to load. Select our added OLE DB connection manager 'DestinationDB' as connection manager and select the specific table name from the drop down.

From the mappings tab of the OLE DB Destination Editor dialog box map the source and destination column. Destination column Id has been ignored because id is identity column of the destination table DataLoadTest.

We have done so far. Let's try to execute the package. To execute the package right click on the package name and click Execute Package. If the package successfully execute it will looks like the below image and if we query our table we will find all data there.

 

Conclusion

We have completed our first article and learn how to create a simple SSIS package. In our next article we will try to learn how to deploy and configure the SSIS package in SQL Server Integration Service Catalog.