Search This Blog

How to Display a variable value in SSIS

Some time in order to debug variables in SSIS you want to see what’s the values that the variable is holding or you want to check the Result set which is stored in the variable.

If you want to show or check the variable value or want to show the value inside a Message box than it can be done through Script task.

Below I will create a test table and will insert a row into the table and will display the ColB value i.e. Hello How are You in SSIS using SQL Execute Task and Script task.

Table script

create table tbl (ColA varchar(50), ColB varchar(50))

insert into tbl  values('Test Message','Hello How are You')

Now we will open SSIS and will drop a SQL Execute task and a Script task in the Package

Follow the below steps

       Double click on SQL execute task

Create a data connection to the database where above table created

In SQL Statement add -select * from tbl

 Select Resultset as Single Row as below Screenshot

  Go to Result tab in the Right side

Add a variable with message and ResultName make it as 1 (Index of your column) Since we will show ColB value in the Message box as below screenshot

Next  connect the SQL Execute task to script task

Now, double click on Script task

Select Read only variable as User Message which we created above as below screenshot

Click on edit script and add Message box (MessageBox.Show(Dts.Variables["Message"].Value.ToString());) inside Main Function as below Screenshot

  Save and Click ok

 Run your Package this will display your variable value in the Message box as below.

Please write in Comments If you'r stuck with any step or need any help.

No comments:

Post a Comment