SSIS - A Basic Demo

Creating SSIS project and getting started

So let’s understand the very first requirement of the project. Goal of this demo is understanding SSIS project basics.

In this Demo we are going to learn, how we can Upload data from a txt file to Sql server database.

We have Customer.txt file as follows. (Make sure you create one and save it somewhere for demo purpose.)

Note: DOB is in “dd/mm/yyyy” format.

First requirement is,loading data from above text file into Customer table in the SSISDB database in Sql server management studio. (Create the database and table in your machine for demo purpose)

Step 1 – Create SSIS project.

Goto File menu and click on New Project Option. 

Select Integration Service Project and click Ok button. (Change the Project name if you want.)

Step 2 – Create New Package

·         In SSIS world Package is an executable file.

·         Visual studio provides a UI interface called SSIS designer for designing packages.

·         Internally package is an XML file which will be executed by special utility called “dtsexec”.This utility will installed as a part of MSBI installation. 

·         Packages will be have an extension called DTSX which stands for “Data transformation services executable”. In earlier version of sql server to perform ETL we had a feature called “Data Transformation services”. DTSX is named after it.

                          

Step 3 – Design Control Flow

Double click the new created package in solution explorer.

As you can see in SSIS designer we have several tabs – Control Flow, Data Flow, Parameters, Event Handlers, and Package Explorer. We will look into each one of these tabs one by one.

Right now we are interested in Control Flow tab.

·         This tab will let us decide what need to be done.

·         You will notice we have SSIS toolbox in left side. If it is not available in your demo then you can get it from View>>Other Windows>>SSIS toolbox.

Toolbox contains tasks like Data Flow Task, Execute Sql Task etc. Each task let us achieve some different behaviour.

·         Right now our requirement asks us to load data from a text file to Sql server database and for that we will be required “DataFlow Task”. Simply drag the task from SSIS toolbox to SSIS designer.

Right click the newly created “Data Flow Task” and select rename. Name it as “CsvCustomer to TblCustomer”.

Step 3 – Add DestinationConnection Manager

Other than all these tabs, SSIS designer also provides something called Connection Managers section. It's located in the bottom Corner of the designer.

Simply right click the area and select “New Ado.Net Connection…”

Click New button

Enter Server Name, Enter Credential, and Select Database and click OK.

Click Ok again.

Rename connection manager if you want.

Step 4 – Add SourceConnection Manager

Now it’s time to add Source Connection Manager.

Right click the connection manager area but this time select “New Flat file Connection...”

It will launch “Flat file Connection manager editor”.

In left side of the dialog couple of sections are defined like General, Columns, and Advanced etc.

Select Columns sections. No need to change any settings at this moment. Simply click Ok.

                          

Step 5 – Configure Data Flow Task

After that double click Data Flow task. It will take you to Data flow tab.

Step 6 –Add Source

Data flow tab is the one which will actually decide ETL. Here we will define, from where to where data will flow and if there is any transformation required or not.

As soon as you move to the Data Flow tab, you will notice a change in the SSIS toolbox.

As you can see, tasks in the toolbox is segregated into three groups – Sources, Transforms and Destinations. “Common” is a special group which contain mostly used sources, transformations and destinations.

We are interested in “Flat file source”. You will find it in “Other Sources” section. Simply drag it to SSIS designer and rename it to “CustomerCsv”

Step 7 – Configure Source

Double click the CustomerCsv source. It will launch “Flat file source editor”. Select “Flat File Connection Manager” from the dropdown.

Move to the column section and make any changes if required and click ok.

Step 8 – Add Destination

Drag Ado.Net destination from “Other Destinations” section to SSIS designer and rename it to “TblCustomer”. 

Step 9 – Configure Destination

Destination task cannot configured unless and until it have a proper input.

Now click the “CustomerCsv” source. You will notice two arrows coming out of it. Blue one and red one. We will talk about red one later. Blue one is the one though which data will flow. Hence this arrow is called as “Data Flow Path”. Take that arrow and connect it to “TblCustomer” destination.

Step 10- Configure Destination (Continued)

Double click the “TblCustomer” destination again. In the “configuration editor window” select Connection manager from dropdown and table to “Customer”

Click on “Mappings” section and confirm that all mappings are correct.

Click ok.

Step 11- Execute and Test the package

As I said sometime back, Package will be executed by a special utility called “DtsExec.exe”. Visual studio makes our life easy at the time of development. For testing simply press F5 ☻ everything else will be handled by Visual studio and package start executing.

Open database table and check the records.

Finally achieved.☻☻☻

Reference: https://www.codeproject.com/