PowerShell Virtual Group - PowerShell ❤ SSIS

Last week I had the honor to speak for the PowerShell Virtual Group about the combination of SSIS and PowerShell. As promised, here is my PowerPoint of that session and a list of all my PowerShell scripts for SSIS:

And on my other blog I have a couple of PowerShell scripts available for Azure which can be executed as runbooks in Azure Automation. And if you have an hour available you can watch the entire session on youtube.

Read content of Object variable

Case
I am filling an Object variable with an Execute SQL Task and I want to use it in a Foreach Loop Container (Foreach ADO Enumerator), but the Foreach Loop stays empty. So I want to check the value of my Object variable. However debugging the package does not show me the value of Object variables. How can I see the content of my Object variable?

No (readable) value for Object variables

Solution
A solution could be to use a Script Task after the Execute SQL Task to show the content of the Object variable. The script below shows the top (x) records in a MessageBox. The code doesn't need any changes. The only change that you could consider to make is changing the number of records to show in the MessageBox (see C# variable maxRows).

Getting content of Object variable

1) Add a Script Script Task
Add a new Script Task to the surface of your Control Flow and connect it to your Execute SQL Task. Then edit the Script Task to provide one Object variable in the property ReadOnlyVariables or ReadWriteVariables. This should of course be the same Object variable as in your Execute SQL Task.

Provide one Object variable

2) Edit Script
Make sure to select Microsoft Visual C# as Script Langugage and then hit the Edit Script button to open the Vsta environment. Then first locate the Namesspaces to add an using for System.Data.OleDb.

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.OleDb;    // Added
#endregion

Then scroll down and located the Main method "public void Main()" and replace it with the code below.

public void Main()
{
    // Show max number of data rows in a simgle messagebox
    int maxRows = 3;

    /////////////////////////////////////////////////////////////////////
    // No need to change lines below
    /////////////////////////////////////////////////////////////////////

    // Create a table object to store the content of the object variable
    OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
    DataTable myTable = new DataTable();

    // Create message string to show the content of the object variable
    string message = "";
    string header = "Error";

    // Five checks before looping through the records in the object variable
    ////////////////////////////////////
    // 1) Is a variable provided?
    ////////////////////////////////////
    if (Dts.Variables.Count.Equals(0))
    {
        message = "No read-only or read-write variables found";
    }
    ////////////////////////////////////
    // 2) Multiple variables provided
    ////////////////////////////////////
    else if(Dts.Variables.Count > 1)
    {
        message = "Please provide only 1 read-only or read-write variable";
    }
    ////////////////////////////////////
    // 3) Is it an object variable?
    ////////////////////////////////////
    else if (!Dts.Variables[0].DataType.ToString().Equals("Object"))
    {
        message = Dts.Variables[0].Name + " is not an Object variable";
    }
    ////////////////////////////////////
    // 4) Is it null or not an table?
    ////////////////////////////////////
    else
    {
        try
        {
            // Try to fill the datatable with the content of the object variable
            // It will fail when it is null or not containing a table object.
            dataAdapter.Fill(myTable, Dts.Variables[0].Value);
        }
        catch
        {
            // Failing the third check
            message = Dts.Variables[0].Name + " doesn't contain a usable value";
        }
    }

    ////////////////////////////////////
    // 5) Is it containing records
    ////////////////////////////////////
    if (myTable.Rows.Count > 0)
    {
        int j = 0;
        // Loop through all rows in the dataset but don't exceed the maxRows
        for (j = 0; j < myTable.Rows.Count && j < maxRows; j++)
        {
            // Get all values from a single row into an array
            object[] valuesArray = myTable.Rows[j].ItemArray;

            // Loop through value array and columnnames collection
            for (int i = 0; i < valuesArray.Length; i++)
            {
                message += myTable.Rows[j].Table.Columns[i].ColumnName + " : " + valuesArray[i].ToString() + Environment.NewLine;
            }
            // Add an empty row between each data row
            message += Environment.NewLine;
        }

        // Create header
        header = "Showing " + j.ToString() + " rows out of " + myTable.Rows.Count.ToString();
    }
    else if (!message.Equals(""))
    {
        // Don't do anything
        // Record count is 0, but an other validition already failed
    }
    else
    {
        // Record count is 0
        message = Dts.Variables[0].Name + " doesn't contain any rows";
    }

    // Show message with custom header
    MessageBox.Show(message, header);

    Dts.TaskResult = (int)ScriptResults.Success;
}

Now close the Vsta environment and click on OK in the Script Task editor to finish it.


3) The result
Now run the package to see the result. I tried to make it a bit monkey proof by adding some checks in the code. If you provide a good and filled variable then it will show the data. Otherwise it will show an error telling you what's wrong.

The result