How to transfer the variables from execute sql task to script task in ssis.

In this example I am fetching records from persons table using execute sql task and the same record I want to add in customer table, to do this I created one stored procedure called “insertcustoner” , this stored procedure will insert records in customer table and we are  calling it in script task(c # code).

Step1: Declare variables in package.

Step2: Drag and drop one execute sql task from toolbar. Click on that make result set property to “full set”, maintain all other property as show in picture…

 

Step3: Add result set variable(this variable will contain all records fetched from persons table ).

 

Step4: Drag and drop one ForEach loop container, click on that go to collections select “ForEach ADO Enumerator” then select ADO Object Source variable, here you have to select the result set variable of previous step.

 

Step5: Do the mapping of variable which you already fetched from persons table(here one variable means the one record fetched from one column and the variable numbers are limited to number of columns you fetched)

 

Step6: Drag and drop one script task from toolbar, click on that, in script option add read only variables those you already declare in mapping of ForEach loop. Or the number of columns you fetched in second step.

Step7: Then add following code.This code will take  three parameters as input pid, name, address. These parameters contains the  one single row from result set variable. In this code the “insertcustoner” is a stored procedure name.

Note: We can call package variables using this code.

 Dts.Variables["MyCaseSensitiveVariableName"].Value;

string cs=@"server=NWS1026\SQLEXPRESS;database=practice;trusted_connection=yes";

SqlConnection conn = new SqlConnection(cs);

SqlCommand command = new SqlCommand("insertcustoner", conn);

command.CommandType = CommandType.StoredProcedure;

command.Parameters.Add("@name", SqlDbType.VarChar).Value = Dts.Variables["name"].Value;

command.Parameters.Add("@pid", SqlDbType.VarChar).Value = Dts.Variables["pid"].Value;

command.Parameters.Add("@address", SqlDbType.VarChar).Value = Dts.Variables["address"].Value;

//command.Parameters.Add("@Password", SqlDbType.VarChar).Value = password;

conn.Open(); int rows = command.ExecuteNonQuery();

conn.Close();