In Technologies
0
196

The full-form of SSIS is SQL Server Integration Services and this is used to do ETL operation where, ETL is Extract, Transform and Load data. It helps us to answer the complex business questions like copying or downloading files and keep updating data in our warehouse or data source sometime used for cleaning and managing SQL Server objects and data.

Minimum Requirements

  • 64-Bit Windows XP or newer
  • Microsoft SQL Server 2008 or newer
  • 6 GB hard-disk space
  • 4 GB RAM
  • SQL Server Data Tool

Creation of first SSIS package

Following step-by-step process can be followed for its further processing:

  • After installation of SQL Data Tool, you will get a new option Integration Services Project in Visual Studio under Business Intelligence.

  • After clicking on OK button a project with empty package get loaded and the solution explorer will be like the screenshot, which is on the left side. The project name is SSIS_DEMO and the default SSIS package name is package.dtsx

  • There are five tabs in one package namely Control Flow, Data Flow, Parameters, Event Handlers and Package Expression.

Step By Step Creation Of SSIS Package

  • From toolbox drag and drop one SCRIPT TASK  and  DATA FLOW TASK under control flow tab and rename if you want to.

SCRIPT TASK

In this, we use to write a script to manipulate data, show messages, check condition or do anything which you relate to do on the script.

On double click of script task and a popup box will appear with an option of edit script with suitable languages like C# and VB, click on edit script button that will lead to another project and you have to write your code under main and save the file. Here’s a screenshot below:

public void Main()
{
   // TODO: Add your code here
      MessageBox.Show("All about to import data.");
      Dts.TaskResult = (int)ScriptResults.Success;
}

DATA FLOW TASK

Double click on data flow task will leads you to Data Flow tab where actual data gets transferred from source to destination in this sample package we have to drag and drop one OLE DB Source (have to configure the connection from SQL) and Excel Destination in which data of database get to export to excel file with the help of this package, on source double click you have to select screenshot and table from which data need to export. Since we are going to learn SSIS topic wise.

1. SSIS Package To Export Data From Flat File To Database Table

On right clicking packages folder in solution explorer select a option new package and rename that .dtsx file to “FlatFileToTable“. On Control Flow tab drag and drop and in Data Flow tab, “Flat File Source” and “OLE DB Destination” but that flat file should be in comma separated or pipe separated after that create a flat file connection, I’ll give that sample flat file below.

Step 1

Step 2

Data Conversion is a whole new topic which we will discuss after this topic. Now, you just have double click on it and convert the data type of source column into that type in which SQL server ready to accept that.

Like our flat file contains 2 column (ID & CountryName) and after linking both tools with each other by linking blue arrow from source to destination, now we are going to select one database table of the same types in our OLE DB source table name is Country and map the column of the flat file/convert converted column from the left panel to the tables column on the right panel.

After doing all these things, just run your SSIS package by right click into a package in solution explorer and execute.

If any doubt still come up in your mind then check out my video of the creation of this SSIS package in the link below:

2. SSIS Package To Export Data From Database Table To Excel File

On right clicking, SSIS packages folder in solution explorer select an option New SSIS Package that will create a new package and do rename that .dtsx file to “TableToExcelFile.dtsx“.

On Control Flow tab drag and drop the Data Flow Task and in Data Flow tab drag and drop OLE DB Source.

Create one connection under connection manager folder for SQL DataSource by right click on folder after that you have to choose OLE DB type of connection string and click on the new button to provide SQL Server login credentials.

On double click of OLE DB Data Source, select connection string and table from which you want to export data.

There’s one more connection string we have to create for the output excel file and for that please follow simple steps given below:

  1. Right click on Connection Manager and select new connection option.
  2. Choose Excel type of connection and click on Add button as per given image SS No 1.
  3. There’s one more dialog box popup in which you have to type excel file name and the version excel. SS No 2
  4. Click OK button and link both components by dragging the blue arrow from the source component to destination components.

SS No 1

SS No 2

After completion of the above steps execute your package and you see a green tick on both of the components indicating the package ran successfully.

As a result, here’s output file which I have generated from database table to Excel file.

In case of any further doubt, check out the video of the creation of this SSIS package in the link below:

Data Conversion

Before learning more topics on SSIS, first of all, let’s check out what is data conversion in SSIS packages.

The Data Conversion component changes/transfer or convert the input column value into the desired datatype.

For example

In my first SSIS package where we export data from flat file to database table and in that SSIS we use Data Conversion component, changing the string value into Unicode DT_WSTR.

Basically, whenever we do change datatype it will create a new output column to map on with prefix of “Copy of” like if we are changing FirstName column datatype then it will change into Copy of FristName 

At the time data conversion, there are some fields which we need to be changed.

SSIS Data Type SSIS Expression SQL Server
two-byte signed integer (DT_I2) smallint
four-byte signed integer (DT_I4) int
eight-byte signed integer (DT_I8) bigint
single-byte unsigned integer (DT_UI1) tinyint
float (DT_R4) real
double-precision float (DT_R8) float
string (DT_STR, «length», «code_page») char, varchar
Unicode text stream (DT_WSTR, «length») nchar, nvarchar, sql_variant, xml
date (DT_DATE) date
Boolean (DT_BOOL) bit
numeric (DT_NUMERIC, «precision», «scale») decimal, numeric
decimal (DT_DECIMAL, «scale») decimal
currency (DT_CY) smallmoney, money
unique identifier (DT_GUID) uniqueidentifier
byte stream (DT_BYTES, «length») binary, varbinary, timestamp
database date (DT_DBDATE) date
database time with precision (DT_DBTIME2, «scale») time(p)
database timestamp (DT_DBTIMESTAMP) datetime, smalldatetime
database timestamp with precision (DT_DBTIMESTAMP2, «scale») datetime2
database timestamp with timezone (DT_DBTIMESTAMPOFFSET, «scale») datetimeoffset(p)
image (DT_IMAGE) image
text stream (DT_TEXT, «code_page») text
Unicode string (DT_NTEXT) ntext

In this blog, I have tried providing a simple introduction to SSIS in the most simplest form. Please feel free to comment your queries below.

In my next blog I will explain following topics:

  1. SQL Server Integration Services Variables
  2. SQL Server Integration Services Expressions
  3. Table To Another Table Data Transfer
RECOMMENDED POSTS

Start typing and press Enter to search