How To Create SSIS Package, Example With Diagrams

In this post we will try to crate a package that extract the data from 'Student Table' of Source-Database and load it successfully in 'Student Table' of 'Destination-Database'. You can Download or look into the sample database I am using.

System requirements for SSIS or Business intelligence Development:

1. You should have installed SQL Server Standard or Enterprise version.
2. I am using Visual Studio Add-in (SQL Server Data Tools) in this SSIS tutorial to design SSIS package. This needs SSDT also to be installed.

Once confirmed that SSDT is installed on machine. We are ready to create an ETL(Extract - Transform - Load) package.

So here we go!

Here are the scripts that you can run in order to generate two databases(Source and destination) with Tables in it.

Go to Start Screen or Click Start Button. Go to Microsoft SQL Server and you will find 'SQL Server Business Intelligence Development Studio' under this.

Open SQL Server business Intelligence Development Studio

This opens a instance of Visual Studio. Click on File tab and select 'New Project'. Chose 'Business Intelligence Projects' as project type and select 'Integration Services Project', type a sensible name for your project(I have given it Simple Data Transfer, that's what it does.). you can chose a location and whether you want to create a separate directory for your project under selected hard-drive location.

Selecting  project template for SSIS using visual studio.

This opens a Package.dtsx file on visual editor. There are 2 sections on left, and 2 on right side of Editor which are:

1. Variables: Here variables can be defined that can later used during execution of ETL or package. user should mention a appropriate scope for the variable defined.
2. ToolBox: As normal toolbox control of visual studio, contains all the necessary tools to crate a package under SSIS
3. Solution Explorer: Hierarchy of the folders of the current project.
4. Properties: Shows available properties for selected module/component from editor.

There are also 4 tabs on editor window namely

1. Control Flow: The main container where everything else resides.
2. Data Flow: Data Flow is the main component when we have to transfer data.

Other than these there are 2 more: Event Handlers and Package Explorer(skip for now).

Open Toolbox and double click 'Data Flow Task' or drag-drop it on editor window. This is the only task needed for this 'Simple Data Transfer' ETL Project. Double click on the 'Data Flow Task' in editor and it opens a editor for selected Data Flow Task.

Double Click the 'Data Flow Task'. Select 'OLE DB Source' from toolbox and drag it to editor. as we have to transfer the data from a SQL Server Database.

Note:

1. Flat File Source should be chosen when source is flat file.

2. Excel Source should be chosen when source is excel file. 

Double Click the 'OLE DB Source' component to configure it. Here click on 'Connection Manager' menu and select an available connection. If it seems empty in here and no connection is available then click on 'Cancel' button.

On 'Data Flow' editor, there is a 'Connection Manager' section at bottom. Right Click on this section and select 'New OLE DB Connection...'

This opens a 'Connection Manager' window. Select the server here and connect to a database selecting it from drop-down box. To log on to server either use windows Authentication or use SQL Server Authentication credentials of the user you are logging in with. Click 'OK'. This will create a new connection manager to connect with.

Now 'OLE DB Source' can be configured using this new connection. select the appropriate connection from list and say 'OK' pressing the button obviously :)

Once done with setting-up the database connection for source, chose the table to extract data from.(Student in this case). Click 'OK' and we are done with 'OLE DB Source' configuration.

Now drag-drop a 'OLE DB Destination' component from toolbox on editor window, click on 'OLE DB Source' and connect the Green Line to 'OLE DB Destination.' And Configure the destination database same as we configured source database.(just select destination databse while you select database and not source)

Now its the time to disco (mapping actually). Let us now map the columns of source and destination tables. Click on 'Mapping' menu in 'OLE DB Destination' configuration, and there will be 2 tables connecting by some links to appropriate columns.

Click on the first link connecting IDs of both the tables and delete it. As we have defined ID column for tables as Identity it will get auto-generated value by itself.

That's fine for now. We don't need so many configuration for this easy task. Save your package file and go to 'Control Flow' tab. Right click on 'Data Flow task' and click on 'Execute task'. This will execute the task and transfer the data from source table to destination table.

If it runs successfully(chances are 100% if your are following step by step ETL creation till now.), it will turn 'GREEN'.

But if it turns 'RED' don't worry, there is a tab 'Execution Result'. This tab shows the progress of the package execution. If you get an error it will be shown here. Copy the error in a notepad and check the issue.

Or if you want you can also debug SSIS package using debugging events. Right click on 'Data Flow Task' and set debugging brake points.

Cheers and comment or share if this helps!!