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




Sunday, 14 May 2017

Import and export SSIS Catalog Environments with JSON

Case
I want to import and export Environments to/from my SSIS Catalog. Doing it manually in SSMS takes ages. How can you do that more quickly?
I want to export this environment



















Solution
I will ago I created a couple of PowerShell scripts to deploy environments to your SSIS Catalog with a CSV file, database table or an array as source. The script below is a follow up that allows you to export one or more environments as json files, but also has an import method to deploy those exported environments to a Catalog: Get-CatalogEnvironment and Set-CatalogEnvironment.

This is an example of how you execute the two methods. It first starts with importing a separate script file with various methods and then you can either execute the Get or the Set method:
# PowerShell code
# If you have trouble executing a PowerShell due an Execution Policy then run
# the following script. Note that you need to run PowerShell as administrator
# More information: https://technet.microsoft.com/nl-nl/library/ee176961.aspx
# Set-ExecutionPolicy Unrestricted 

# Include functions from a secondairy file
. "$PSScriptRoot\Ssisfunctions.ps1"

# Download example
Get-CatalogEnvironment -SsisServer "mySqlServer\myInstance" -ExportPath "c:\backup\" -FolderName MyEnvFolder -EnvironmentName MyEnvName -Verbose

# Upload example
Set-CatalogEnvironment -SsisServer "mySqlServer\myInstance" -FolderName MyEnvFolder -EnvironmentName MyEnvName -ImportFilePath "C:\temp\employees.json" -DeleteExistingEnvironment $true -Verbose
Example of execution














The environment json files look like this:
[
    {
        "Name":"FolderStageFiles",
        "Description":"Location of stage files",
        "Type":"String",
        "Sensitive":false,
        "Value":"d:\\sources\\"
    },
    {
        "Name":"FtpPassword",
        "Description":"Secret FTP password",
        "Type":"String",
        "Sensitive":true,
        "Value":$3cr3t
    },
    {
        "Name":"MIS_STG_Connectionstring",
        "Description":"Connectionstring to stage database",
        "Type":"String",
        "Sensitive":false,
        "Value":"Data Source=.\\sql2016;Initial Catalog=MIS_STG;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;"
    },
    {
        "Name":"NumberOfRetries",
        "Description":"Number of retries for Webservice Task",
        "Type":"Int16",
        "Sensitive":false,
        "Value":3
    }
]

You can also get detailed help information and instructions with the standard PowerShell method. Get-Help. It allows you to see examples or the see which parameter is mandatory or optional.
# PowerShell code
# Getting help about the commands
Get-Help Set-CatalogEnvironment -detailed
Get-Help Get-CatalogEnvironment -example

And this is the content of the Ssisfunctions.ps1 file containing the various methods. Take a look and let me know if you have any improvements
# PowerShell code: Ssisfunctions.ps1 (v0.1)
<#
.Synopsis
    Download one or more environments from an SSIS Catalog as JSON files

.DESCRIPTION
    This functions allows you to download an Environment from the SSIS Catalog. By leaving out the foldername or environmentname you can also download
    multiple files. All files are downloaded as JSON files in the format [FolderName].[EnvironmentName].json
    Example file of export:

    [
       {
          "Name":"FolderStageFiles",
          "Description":"Location of stage files",
          "Type":"String",
          "Sensitive":false,
          "Value":"d:\\sources\\"
       },
       {
          "Name":"FtpPassword",
          "Description":"Secret FTP password",
          "Type":"String",
          "Sensitive":true,
          "Value":null
       },
       {
          "Name":"MIS_STG_Connectionstring",
          "Description":"Connectionstring to stage database",
          "Type":"String",
          "Sensitive":false,
          "Value":"Data Source=.\\sql2016;Initial Catalog=MIS_STG;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;"
       },
       {
          "Name":"NumberOfRetries",
          "Description":"Number of retries for Webservice Task",
          "Type":"Int16",
          "Sensitive":false,
          "Value":3
       }
    ]

.PARAMETER SsisServer
    Mandatory: The name of the SQL Server instance that runs the SSIS Catalog

.PARAMETER FolderName
    Optional: The name of the Catalog folder that contains the Environment

.PARAMETER EnvironmentName
    Optional: The name of the Environment

.PARAMETER ExportPath
    Optional: The fully qualified path where the json files will be saved. Default value: c:\temp\

.PARAMETER Verbose
    Optional: Get more logging information on the screen

.EXAMPLE
    Get-CatalogEnvironment -SsisServer "myServer\myInstance" -ExportPath "c:\backup\" -FolderName myCatalogFolder -EnvironmentName myEnvironmentName

.EXAMPLE
    Get-CatalogEnvironment -SsisServer "myServer\myInstance" -ExportPath "c:\backup\" -Verbose

.NOTES
    You cannot get the value of sensitive variables.The value will be NULL in the export file. 
    Current scripts works for SSIS 2016. Change version number in code to use an other version of SSIS.

.LINK 
    https://microsoft-ssis.blogspot.com/
#>
Function Get-CatalogEnvironment
{
    [CmdletBinding()]
    Param
    (
        [Parameter(Mandatory=$true, Position=0)]
        [ValidateLength(1,50)] # String must be between 1 and 50 chars long
        [string]$SsisServer,

        [Parameter(Mandatory=$false, Position=1)]
        [ValidateLength(1,128)] # String must be between 1 and 128 chars long 
        [string]$FolderName,

        [Parameter(Mandatory=$false, Position=2)]
        [ValidateLength(1,128)] # String must be between 1 and 128 chars long 
        [string]$EnvironmentName,

        [Parameter(Mandatory=$false, Position=3)]
        [string]$ExportPath = "C:\temp\" 
    )

    # Don't continue after error
    $ErrorActionPreference = "Stop"

    #################################################
    ############## SHOW ALL PARAMETERS ##############
    #################################################
    Write-Verbose "========================================================="
    Write-Verbose "==      Used parameters - Get-CatalogEnvironment       =="
    Write-Verbose "========================================================="
    Write-Verbose "SSISServer              : $($SsisServer)"
    Write-Verbose "FolderName              : $($FolderName)"
    Write-Verbose "EnvironmentName         : $($EnvironmentName)"
    Write-Verbose "ExportPath              : $($ExportPath)"
    Write-Verbose "========================================================="

 
    #################################################
    ############### ADD SSIS ASSEMBLY ###############
    #################################################
    # Change assembly version number to use an other SSIS version
    # 13.0.0.0 = SSIS 2016
    # 12.0.0.0 = SSIS 2014
    # 11.0.0.0 = SSIS 2012
    $SsisNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
    Add-Type -AssemblyName "$($SsisNamespace), Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
 
 
    #################################################
    ############ CONNECT TO SSIS SERVER #############
    #################################################
    # First create a connection to SQL Server
    $SqlConnectionstring = "Data Source=$($SsisServer);Initial Catalog=master;Integrated Security=SSPI;"
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnectionstring
 
    # Then use that SQL connection to create an
    # Integration Services object.
    $IntegrationServices = New-Object $SsisNamespace".IntegrationServices" $SqlConnection

    # Check if connection succeeded
    If (!$IntegrationServices)
    {
        Throw [System.Exception] "Failed to connect to server $($SsisServer)"
    }
    Else
    {
        Write-Verbose "Connected to server $($SsisServer)"
    }
 
 
    #################################################
    ########### CONNECT TO SSIS CATALOG #############
    #################################################
    # Create object for SSISDB Catalog
    $Catalog = $IntegrationServices.Catalogs["SSISDB"]
 
    # Check if the SSISDB Catalog exists
    If (!$Catalog)
    {
        # Catalog does not exist.
        Throw [System.Exception] "SSISDB catalog does not exist"
    }
    Else
    {
        Write-Verbose "SSISDB catalog found"
    }


    #################################################
    ############## CHECK EXPORT FOLDER ##############
    #################################################
    # Check if folder exists
    If (-Not (Test-Path $ExportPath))
    {
        # Create new folder
        New-Item -ItemType directory -Path $ExportPath | Out-Null
        Write-Host "Folder created: " $ExportPath
    }
    Else
    {
        Write-Verbose "Folder $($ExportPath) found"
    }


    #################################################
    ############# LOOP THROUGH FOLDERS ##############
    #################################################
    # Loop though all folder or filter on a folder name
    Foreach ($Folder in $Catalog.Folders | WHERE {$_.Name -eq $FolderName -or (!$FolderName)})
    {
        # Loop though all environments or filter on a environment name
        Foreach ($Environment in $Folder.Environments | WHERE {$_.Name -eq $EnvironmentName -or (!$EnvironmentName)})
        {
            Write-Host "Exporting $($ExportPath)$($Folder.Name).$($Environment.Name).json"
            $Environment.Variables | Select-Object -Property Name,Description,@{Name='Type';Expression={"$($_.Type)"}},Sensitive,Value | ConvertTo-Json -Compress | Out-File "$($ExportPath)$($Environment.Parent.Name).$($Environment.Name).json"

            # Show warnings if the environment contains sensitive variables
            $Environment.Variables | Select-Object -Property Name,Sensitive | Where {$_.Sensitive -eq $True} | ForEach-Object {
                Write-Warning "Variable $($_.Name) is sensitive. Cannot retrieve its value"
            }
        }
    }

}

<#
.Synopsis
    Upload a json environment file to an SSIS Catalog

.DESCRIPTION
    This functions allows you to upload an Environment to the SSIS Catalog. It can update (no deletes) or replace an existing environment.
    Example file which can be imported:

    [
       {
          "Name":"FolderStageFiles",
          "Description":"Location of stage files",
          "Type":"String",
          "Sensitive":false,
          "Value":"d:\\sources\\"
       },
       {
          "Name":"FtpPassword",
          "Description":"Secret FTP password",
          "Type":"String",
          "Sensitive":true,
          "Value":$3cr3t
       },
       {
          "Name":"MIS_STG_Connectionstring",
          "Description":"Connectionstring to stage database",
          "Type":"String",
          "Sensitive":false,
          "Value":"Data Source=.\\sql2016;Initial Catalog=MIS_STG;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;"
       },
       {
          "Name":"NumberOfRetries",
          "Description":"Number of retries for Webservice Task",
          "Type":"Int16",
          "Sensitive":false,
          "Value":3
       }
    ]

.PARAMETER SsisServer
    Mandatory: The name of the SQL Server instance that runs the SSIS Catalog

.PARAMETER FolderName
    Mandatory: The name of the Catalog folder where the Evironment will be stored

.PARAMETER EnvironmentName
    Mandatory: The name of the Environment

.PARAMETER ImportFilePath
    Mandatory: The fully qualified path of the json file that needs to be imported

.PARAMETER DeleteExistingEnvironment
    Optional: Setting to $true first deletes an existing environment. Default value: $false 

.PARAMETER Verbose
    Optional: Get more logging information on the screen

.EXAMPLE
    Set-CatalogEnvironment -SsisServer "MYSERVER\myInstance" -FolderName MyEnvFolder -EnvironmentName MyEnvName -ImportFilePath "C:\backup\Environments.Generic.json" -DeleteExistingEnvironment $true

.EXAMPLE
    Set-CatalogEnvironment -SsisServer "MYSERVER\myInstance" -FolderName MyEnvFolder -EnvironmentName MyEnvName -ImportFilePath "C:\backup\Environments.Generic.json" -Verbose

.NOTES
    You cannot insert null values. The will be skipped with a warning
    Current scripts works for SSIS 2016. Change version number in code
    to use an other version of SSIS.

.LINK 
    https://microsoft-ssis.blogspot.com/
#>
Function Set-CatalogEnvironment
{
    [CmdletBinding()]
    Param
    (
        [Parameter(Mandatory=$true, Position=0)]
        [ValidateLength(1,50)] # String must be between 1 and 50 chars long
        [string]$SsisServer,

        [Parameter(Mandatory=$true, Position=1)]
        [ValidateLength(1,128)] # String must be between 1 and 128 chars long 
        [string]$FolderName,

        [Parameter(Mandatory=$true, Position=2)]
        [ValidateLength(1,128)] # String must be between 1 and 128 chars long 
        [string]$EnvironmentName,

        [Parameter(Mandatory=$true, Position=3)]
        [ValidateScript({Test-Path -Path $_ -PathType Leaf})] # File must exist
        [ValidatePattern(‘.json$’)] # Extension must be .json
        [string]$ImportFilePath,

        [Parameter(Mandatory=$false, Position=4)]
        [bool]$DeleteExistingEnvironment = $false
    )

    # Don't continue after error
    $ErrorActionPreference = "Stop"

    #################################################
    ############## SHOW ALL PARAMETERS ##############
    #################################################
    Write-Verbose "========================================================="
    Write-Verbose "==      Used parameters - Set-CatalogEnvironment       =="
    Write-Verbose "========================================================="
    Write-Verbose "SSISServer                : $($SsisServer)"
    Write-Verbose "FolderName                : $($FolderName)"
    Write-Verbose "EnvironmentName           : $($EnvironmentName)"
    Write-Verbose "ImportFilePath            : $($ImportFilePath)"
    Write-Verbose "DeleteExistingEnvironment : $($ImportFilePath)"
    Write-Verbose "========================================================="


    #################################################
    ############### ADD SSIS ASSEMBLY ###############
    #################################################
    # Change assembly version number to use an other SSIS version
    # 13.0.0.0 = SSIS 2016
    # 12.0.0.0 = SSIS 2014
    # 11.0.0.0 = SSIS 2012
    $SsisNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
    Add-Type -AssemblyName "$($SsisNamespace), Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

 
    #################################################
    ############ CONNECT TO SSIS SERVER #############
    #################################################
    # First create a connection to SQL Server
    $SqlConnectionstring = "Data Source=$($SsisServer);Initial Catalog=master;Integrated Security=SSPI;"
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnectionstring
 
    # Then use that SQL connection to create an
    # Integration Services object.
    $IntegrationServices = New-Object $SsisNamespace".IntegrationServices" $SqlConnection

    # Check if connection succeeded
    If (!$IntegrationServices)
    {
        Throw [System.Exception] "Failed to connect to server $($SsisServer)"
    }
    Else
    {
        Write-Verbose "Connected to server $($SsisServer)"
    }
 
 
    #################################################
    ########### CONNECT TO SSIS CATALOG #############
    #################################################
    # Create object for SSISDB Catalog
    $Catalog = $IntegrationServices.Catalogs["SSISDB"]
 
    # Check if the SSISDB Catalog exists
    If (!$Catalog)
    {
        # Catalog does not exist. Different name used?
        Throw [System.Exception] "SSISDB catalog does not exist"
    }
    Else
    {
        Write-Verbose "SSISDB catalog found"
    }


    #################################################
    ################## CHECK FOLDER #################
    #################################################
    # Create object to the (new) folder
    $Folder = $Catalog.Folders[$FolderName]
 
    # Check if folder exists
    If (!$Folder)
    {
        # Folder doesn't exists, so create the new folder.
        Write-Host "Creating new folder $($FolderName)"
        $Folder = New-Object $SsisNamespace".CatalogFolder" ($Catalog, $FolderName, $FolderName)
        $Folder.Create()
    }
    Else
    {
        Write-Verbose "Folder $($FolderName) found"
    }


    #################################################
    ################## ENVIRONMENT ##################
    #################################################
    # Create object for the (new) environment
    $Environment = $Folder.Environments[$EnvironmentName]

    # Check if folder already exists
    If (-not $Environment)
    {
        Write-Host "Creating new environment $($EnvironmentName) in $($FolderName)"

        $Environment = New-Object $SsisNamespace".EnvironmentInfo" ($Folder, $EnvironmentName, $EnvironmentName)
        $Environment.Create()
    }
    ElseIf($DeleteExistingEnvironment -and $Environment)
    {
        Write-Verbose "Environment $($EnvironmentName) found with $($Environment.Variables.Count) existing variables"
        Write-Host "Dropping and recreating environment $($EnvironmentName) in $($FolderName)"
        $Environment.Drop()
        $Environment = New-Object $SsisNamespace".EnvironmentInfo" ($folder, $EnvironmentName, $EnvironmentName)
        $Environment.Create()
    }
    Else
    {
        Write-Verbose "Environment $($EnvironmentName) found with $($Environment.Variables.Count) existing variables"
    }
    

    #################################################
    ############### GET FILE CONTENT ################
    #################################################
    Write-Verbose "Reading $($ImportFilePath)"
    $EnvironmentInput = Get-Content -Raw -Path $ImportFilePath | ConvertFrom-Json


    #################################################
    ################### VARIABLES ###################
    #################################################
    # Keep track of number of updates and inserts
    $InsertCount = 0
    $UpdateCount = 0

    # Loop through file content
    $EnvironmentInput | Select-Object -Property Name,Description,Type,Sensitive,Value | ForEach-Object {

        # Get variablename from json and try to find it in the environment
        $Variable = $Environment.Variables[$_.Name]

        # Make sure each variable has a value
        If (!$_.Value)
        {
            Write-Warning "Variable $($_.Name) skipped because it has no value"
        }
        else
        {
            # Check if the variable exists
            If (-not $Variable)
            {
                # Insert new variable
                Write-Verbose "Variable $($_.Name) added"
                $Environment.Variables.Add($_.Name, $_.Type, $_.Value, $_.Sensitive, $_.Description)

                $InsertCount = $InsertCount + 1
            }
            else
            {
                # Update existing variable
                Write-Verbose "Variable $($_.Name) updated"
                $Variable.Type = $_.Type
                $Variable.Value = $_.Value
                $Variable.Description = $_.Description
                $Variable.Sensitive = $_.Sensitive

                $UpdateCount = $UpdateCount + 1
            }
        }
    }
    $Environment.Alter()

    Write-Host "Finished, total inserts $($InsertCount)  and total updates $($UpdateCount)"
}

Later on I will add various extra methods for example to test the existence of an environment, to delete an environment, to move an environment, to copy an environment, to rename an environment or to connect an environment to a project. Please let me know if you have any suggestions for extra functionality or improvements!
Related Posts Plugin for WordPress, Blogger...