How to shred object variable from a record set destination to act as data source for a destination.

Problem: How to shred object variable from a record set destination to act as data source for a destination.

Solution:   Creating a Source with the Script Component

If you store the result set of dataflow task inside a record set destination the result will be stored inside an object variable in the run time and to access this a source for another dataflow task for complex sources or temporary sources, we have an option of script component to send the object variable as source.

To know more about how to use a record set destination please go through the following article.

Record Set Destination

Script component can act as source, destination or transformation inside a dataflow task and to read this from a variable we must select the variable inside the read-only variables section.

Example:

In the example, I would like to show we can store data using an object type of variable inside of data flow and it creates the table during run time.

Step 0: Create a package and in the control flow add Data flow task and create a variable with object type select an  ole db source as source and record set destination as destination and map the variable in the destination variable area.

Step 1: Create another dataflow task and drag drop the script component from the transformations and select as source.

Step 2: Edit the Script component task and select the variable in the read-only area.

Step 3: Add Output columns to reference the result of the object variable. Alternatively, you can use an execute sql task and select the variable in the result set section and result type full to generate the output of variable.

Step 4: Edit the Script to read the variable and loop through the dataset of the variable to send a table result set. After editing the script build the solution to make sure no errors are generated.

After hitting edit script there will be a new window and copy the below code to replace the content in the window and build the solution, note this is in C#.

// Add in the appropriate namespaces

using System.Data;

using System.Data.OleDb;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]

public class ScriptMain : UserComponent

{

    public override void CreateNewOutputRows()

    {

        // Set up the DataAdapter to extract the data, and the DataTable object to capture those results

        OleDbDataAdapter da = new OleDbDataAdapter();

        DataTable dt = new DataTable();

        // Extract the data from the object variable into the table

        da.Fill(dt, Variables.Recordset);

        // Since we know the column metadata at design time, we simply need to iterate over each row in

        //  the DataTable, creating a new row in our Data Flow buffer for each

        foreach (DataRow dr in dt.Rows)

        {

            // Create a new, empty row in the output buffer

            OutputBuffer.AddRow();

            // Now populate the columns

            OutputBuffer.Col1 = (dr["Col1"].ToString());

            OutputBuffer.Col2 = int.Parse(dr["Col2"].ToString());

            OutputBuffer.Col3 = int.Parse(dr["Col3"].ToString());

        }

    }

}

Step 5: Select a destination and map the output of the script component to a destination table.

Output:

As we can see from the preview the variable is acting as source and sending the result to destination.