Sunday 25 June 2017

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.

    Thursday 1 June 2017

    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




    Related Posts Plugin for WordPress, Blogger...