Microsoft Integration: Creating a Variable in SSIS

 SSIS Creating a Variable

 Aim: To understand the use of a variable in SSIS and how to create it. In this package, we would create a variable which would count a number of rows in Excel file.

Tools required:

• SSIS tool

• Excel file

Step to create a Variable:

Step 1:

Create an SSIS package and give it an appropriate name and add a data flow task in package.

Step 2:

Double click on Data flow task and a new window would open Data flow. 

Click on the right upper icon (blue rubber sign) and a window will open.

In variable window name your variable and choose the data type.

Here we would choose

Name: Count, Data type: int32  

Step 3: 

Now create a data source for excel file, set up the connection and put row count function below data source.

Click on row count icon and set up the variable count for it.

Step 4:

Now go to control flow and add a script task in the package and make arrow connection with the data flow task.

Data flow task -> Script flow task.

After all connection double click on script task and add count variable as read-only.

In the main function add script to show variable value on screen on post package execution.

Script: 

 MessageBox.Show("Rows in excel file is = " + Dts.Variables["User::Count"].Value + " value ");

Step 5:

Now when everything is done then it is time to run your package.

Click on the run button and you will see the number of rows in toy file.