How to Pass the a table variable to script task from Execute SQL to Script Task in SQL Server

How to Pass the a table variable to script task from Execute SQL to Script Task in SQL Server

Here I will show how to pass the variable data from one task to other task.
For this first we need a table.

Create an EMP table

create table emp (empid int identity primary key,name varchar(100),salary int); GO insert into emp values ('malls',10000),('reddy',20000),('contract',30000)GO One we have created the scripts execute in the database
Use Execute SQL Task to fetch the values from this table based on emp id.

select empid,name,SALARY from empWHERE empid = 1
 EmpId 1 is the input parameter for execute SQL task.

Create connection string for the database in which the above script executed.

Go to result section and set the result name as 0. For fullresult in ExecuteSQL Task result set must be set to zero.

Variable must be created as Object for full result set. 

 Double Click on Script task and select the Object variable as read only varaiable.

Edit script task

In order to convert the Object into datatable we will use Oledbdataadapter

Using the below namespace.

using System.Data.OleDb;

Copy the below code.

    Dts.TaskResult = (int)ScriptResults.Success;

            DataTable dt = new DataTable("Test");

            OleDbDataAdapter ad = new OleDbDataAdapter();

            ad.Fill(dt, Dts.Variables["User::Variable"].Value);

            dt.WriteXml("D:\\Malls.xml");

            System.IO.StreamWriter sw = new System.IO.StreamWriter("D:\\Malls.csv");

            foreach (DataColumn col in dt.Columns)

            {

                sw.Write(col.ToString()+",");

            }

            sw.WriteLine();

            for (int i = 0;i

            {

                for (int j = 0; j < dt.Columns.Count; j++)

                {

                    sw.Write(dt.Rows[i][j].ToString()+",");

                }

                sw.WriteLine();

               

            }

            sw.Close();

Save the script and execute.

It will create the CSV with the following output.

empid name SALARY

1 malls 10000

2 reddy 20000

3 contract 30000