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

Deploying environments and variables with PowerShell (C: csv)

Case
I want to deploy an environment with variables to the Integration Services Catalog. How do I do that?

Environment with variables

Solution
You can't create them outside SQL Server Management Studio and then deploy them to the catalog. The environment isn't part of the ISPAC file. But it's possible to generate some TSQL code and then execute those Store Procedure calls on all environments of the DTAP.
Or you can use PowerShell script to generate an environment with variables. This blog post shows three different options to use PowerShell to create environments:

1. Store values in (multi-)array
2. Store values in database table
3. Store values in CSV file

C: Store values in CSV file
This example works with two powershell files. The first only contains four parameters. The second file contains the real logic and is called by the first. A benefit of two separate files is that you can use the scripts for multiple environments, but all logic is stored in a single file. In this example the variables are stored in a CSV file instead of an array or database table. You can of course still store the values in a central database and then export the records to a CSV file for the PowerShell script.

A list of all parameters:

SsisServer contains the servername (and instance name) of the Sql Server that has the Integration Services catalog.

EnvironmentName contains the name of the environment. If the environment already exists then it just adds or updates variables. There are deliberately no deletes. but that's possible.

EnvironmentFolderName contains the foldername where the environment is located. If the folder doesn't exists, it will be created.

FilepathCsv contains the path of the CSV file. This example uses $PSScriptRoot to get the location of the current PowerShell file and then concatenates the filename of the CSV to it. You can also use a regular path like d:\folder\EnvironmentVariables.csv.

This example uses strings, Int16 and Boolean, but there are other datatypes like DataTime, Int32, Int64, etc.). The script contains a lot of feedback. If it's to excessive for you, you can skip a couple of Write-Host lines by adding a hashtag in front of it. For deploying it's not necessary to change anything in the second file.

#PowerShell: ParamsForGeneralEnvironmentDeploymentWithCsv.ps1
#################################################################################################
# Change source and destination properties
#################################################################################################
# Ssis
$SsisServer ="."
$EnvironmentFolderName = "Environments"
$EnvironmentName = "Generic"

# Path of CSV containing variables (you can also use format d:\file.csv)
$FilepathCsv = "$PSScriptRoot\EnvironmentVariables.csv"

# Execute deployment script
. "$PSScriptRoot\GeneralEnvironmentDeploymentWithCsv.ps1" $SsisServer $EnvironmentFolderName $EnvironmentName $FilepathCsv

Second file:

#PowerShell: GeneralEnvironmentDeploymentWithCsv.ps1
################################
########## PARAMETERS ##########
################################ 
[CmdletBinding()]
Param(
    # SsisServer is required
    [Parameter(Mandatory=$True,Position=1)]
    [string]$SsisServer,
    
    # EnvironmentFolderName is required 
    [Parameter(Mandatory=$True,Position=2)]
    [string]$EnvironmentFolderName,
    
    # EnvironmentName is required
    [Parameter(Mandatory=$True,Position=3)]
    [string]$EnvironmentName,
    
    # FilepathCsv is required
    [Parameter(Mandatory=$True,Position=4)]
    [string]$FilepathCsv
)

clear
Write-Host "========================================================================================================================================================"
Write-Host "==                                                                 Used parameters                                                                    =="
Write-Host "========================================================================================================================================================"
Write-Host "SSIS Server             :" $SsisServer
Write-Host "Environment Name        :" $EnvironmentName
Write-Host "Environment Folder Path :" $EnvironmentFolderName
Write-Host "Filepath of CSV file    :" $FilepathCsv
Write-Host "========================================================================================================================================================"


#########################
########## CSV ##########
#########################
# Check if ispac file exists
if (-Not (Test-Path $FilepathCsv))
{
    Throw  [System.IO.FileNotFoundException] "CSV file $FilepathCsv doesn't exists!"
}
else
{
    $FileNameCsv = split-path $FilepathCsv -leaf
    Write-Host "CSV file" $FileNameCsv "found"
}


############################
########## SERVER ##########
############################
# Load the Integration Services Assembly
Write-Host "Connecting to SSIS server $SsisServer "
$SsisNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
[System.Reflection.Assembly]::LoadWithPartialName($SsisNamespace) | Out-Null;

# Create a connection to the server
$SqlConnectionstring = "Data Source=" + $SsisServer + ";Initial Catalog=master;Integrated Security=SSPI;"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnectionstring

# Create the Integration Services object
$IntegrationServices = New-Object $SsisNamespace".IntegrationServices" $SqlConnection

# Check if connection succeeded
if (-not $IntegrationServices)
{
  Throw  [System.Exception] "Failed to connect to SSIS server $SsisServer "
}
else
{
   Write-Host "Connected to SSIS server" $SsisServer
}


#############################
########## CATALOG ##########
#############################
# Create object for SSISDB Catalog
$Catalog = $IntegrationServices.Catalogs["SSISDB"]

# Check if the SSISDB Catalog exists
if (-not $Catalog)
{
    # Catalog doesn't exists. The user should create it manually.
    # It is possible to create it, but that shouldn't be part of
    # deployment of packages or environments.
    Throw  [System.Exception] "SSISDB catalog doesn't exist. Create it manually!"
}
else
{
    Write-Host "Catalog SSISDB found"
}


############################
########## FOLDER ##########
############################
# Create object to the (new) folder
$Folder = $Catalog.Folders[$EnvironmentFolderName]

# Check if folder already exists
if (-not $Folder)
{
    # Folder doesn't exists, so create the new folder.
    Write-Host "Creating new folder" $EnvironmentFolderName
    $Folder = New-Object $SsisNamespace".CatalogFolder" ($Catalog, $EnvironmentFolderName, $EnvironmentFolderName)
    $Folder.Create()
}
else
{
    Write-Host "Folder" $EnvironmentFolderName "found"
}


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

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

    $Environment = New-Object $SsisNamespace".EnvironmentInfo" ($Folder, $EnvironmentName, $EnvironmentName)
    $Environment.Create()
}
else
{
    Write-Host "Environment" $EnvironmentName "found with" $Environment.Variables.Count "existing variables"
    # Optional: Recreate to delete all variables, but be careful:
    # This could be harmful for existing references between vars and pars
    # if a used variable is deleted and not recreated.
    #$Environment.Drop()
    #$Environment = New-Object $SsisNamespace".EnvironmentInfo" ($folder, $EnvironmentName, $EnvironmentName)
    #$Environment.Create()
}


###############################
########## VARIABLES ##########
###############################
$InsertCount = 0
$UpdateCount = 0


Import-CSV $FilepathCsv -Header Datatype,ParameterName,ParameterValue,ParameterDescription,Sensitive -Delimiter ';' | Foreach-Object{
 If (-not($_.Datatype -eq "Datatype"))
 {
  #Write-Host $_.Datatype "|" $_.ParameterName "|" $_.ParameterValue "|" $_.ParameterDescription "|" $_.Sensitive
  # Get variablename from array and try to find it in the environment
  $Variable = $Catalog.Folders[$EnvironmentFolderName].Environments[$EnvironmentName].Variables[$_.ParameterName]


  # Check if the variable exists
  if (-not $Variable)
  {
   # Insert new variable
   Write-Host "Variable" $_.ParameterName "added"
   $Environment.Variables.Add($_.ParameterName, $_.Datatype, $_.ParameterValue, [System.Convert]::ToBoolean($_.Sensitive), $_.ParameterDescription)

   $InsertCount = $InsertCount + 1
  }
  else
  {
   # Update existing variable
   Write-Host "Variable" $_.ParameterName "updated"
   $Variable.Type = $_.Datatype
   $Variable.Value = $_.ParameterValue
   $Variable.Description = $_.ParameterDescription
   $Variable.Sensitive = [System.Convert]::ToBoolean($_.Sensitive)

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


###########################
########## READY ##########
###########################
# Kill connection to SSIS
$IntegrationServices = $null 
Write-Host "Finished, total inserts" $InsertCount " and total updates" $UpdateCount

CSV file:



CSV: EnvironmentVariables.csv

Datatype;Parameter Name;Parameter Value;Parameter Description;Sensitive (true or false)
String;MIS_STG_ConnectionString;"Data Source=.\sql2016;Initial Catalog=MIS_STG;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;";Connectionstring to stage database;false
String;MIS_HST_ConnectionString;"Data Source=.\sql2016;Initial Catalog=MIS_HST;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;";Connectionstring to historical stage database;false
String;MIS_DWH_ConnectionString;"Data Source=.\sql2016;Initial Catalog=MIS_DWH;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;";Connectionstring to data warehouse database;false
String;MIS_MTA_ConnectionString;"Data Source=.\sql2016;Initial Catalog=MIS_MTA;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;";Connectionstring to metadata database;false
String;MIS_DM_ConnectionString;"Data Source=.\sql2016;Initial Catalog=MIS_DM;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;";Connectionstring to data mart database;false
String;FtpPassword; 53cr3t!;Secret FTP password;true
String;FtpUser;SSISJoost;Username for FTP;false
String;FtpServer;ftp://SSISJoost.nl;FTP Server;false
String;FolderStageFiles;d:\sources\;Location of stage files;false
Boolean;EnableRetry; true;Enable retry for Webservice Task;false
Int16;NumberOfRetries;3;Number of retries for Webservice Task;false
Int16;SecsPauseBetweenRetry;30;Number of seconds between retry;false

New environment

Existing environment

Download scripts

Tip: if you keep the parameter names in your SSIS projects equal to the variable names in the environment, then you could automatically references them during deployment using this PowerShell script.

Deploying environments and variables with PowerShell (B: table)

Case
I want to deploy an environment with variables to the Integration Services Catalog. How do I do that?

Environment with variables

Solution
You can't create them outside SQL Server Management Studio and then deploy them to the catalog. The environment isn't part of the ISPAC file. But it's possible to generate some TSQL code and then execute those Store Procedure calls on all environments of the DTAP.
Or you can use PowerShell script to generate an environment with variables. This blog post shows three different options to use PowerShell to create environments:

1. Store values in (multi-)array
2. Store values in database table
3. Store values in CSV file

B: Store values in database table
This second example works with two powershell files. The first only contains five parameters. The second file contains the real logic and is called by the first. A benefit of two separate files is that you can use the scripts for multiple environments, but all logic is stored in a single file. In this example the variables are stored in a table instead of an array. An array can't be used as parameter between two separate files.

A list of all parameters:

SsisServer contains the servername (and instance name) of the Sql Server that has the Integration Services catalog.

EnvironmentName contains the name of the environment. If the environment already exists then it just adds or updates variables. There are deliberately no deletes. but that's possible.

EnvironmentFolderName contains the foldername where the environment is located. If the folder doesn't exists, it will be created.

SqlServer contains the servername (and instance name) of the Sql Server that has a database with the table containing the variables. Note that this server should be reachable from all environments where you want to run this PowerShell script.

SqlDatabase contains the database name that has the table containing the variables. 

This example uses strings, Int16 and Boolean, but there are other datatypes like DataTime, Int32, Int64, etc.). The script contains a lot of feedback. If it's to excessive for you, you can skip a couple of Write-Host lines by adding a hashtag in front of it. For deploying it's not necessary to change anything in the second file.

#PowerShell: ParamsForGeneralEnvironmentDeploymentWithTable.ps1
#################################################################################################
# Change source and destination properties
#################################################################################################
# Ssis
$SsisServer ="localhost"
$EnvironmentFolderName = "Environments"
$EnvironmentName = "Generic"

# SqlServer (config table)
$SqlServer ="localhost"
$SqlDatabase ="ssisjoost"

# Execute deployment script
. "$PSScriptRoot\GeneralEnvironmentDeploymentWithTable.ps1"  $SsisServer $EnvironmentFolderName $EnvironmentName $SqlServer $SqlDatabase

Second file:

#PowerShell: GeneralEnvironmentDeploymentWithTable.ps1
################################
########## PARAMETERS ##########
################################ 
[CmdletBinding()]
Param(
    # SsisServer is required
    [Parameter(Mandatory=$True,Position=1)]
    [string]$SsisServer,
    
    # EnvironmentFolderName is required 
    [Parameter(Mandatory=$True,Position=2)]
    [string]$EnvironmentFolderName,
    
    # EnvironmentName is required
    [Parameter(Mandatory=$True,Position=3)]
    [string]$EnvironmentName,
    
    # SqlServer is required
    [Parameter(Mandatory=$True,Position=4)]
    [string]$SqlServer,
    
    # SqlDatabase is required
    [Parameter(Mandatory=$True,Position=5)]
    [string]$SqlDatabase
)

clear
Write-Host "========================================================================================================================================================"
Write-Host "==                                                                 Used parameters                                                                    =="
Write-Host "========================================================================================================================================================"
Write-Host "SSIS Server             :" $SsisServer
Write-Host "Environment Name        :" $EnvironmentName
Write-Host "Environment Folder Path :" $EnvironmentFolderName
Write-Host "Sql Server (for config) :" $SqlServer
Write-Host "Database (for config)   :" $SqlDatabase
Write-Host "========================================================================================================================================================"


###############################
########## VARIABLES ##########
###############################
# Get variables from table

# Load SqlServer.SMO assembly to get data out of SQL Server
Write-Host "Connecting to SSIS server $SqlServer "
Add-Type -AssemblyName "Microsoft.SqlServer.SMO, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

# Create SQL Server object using integrated security 
$SqlServerConf = New-Object Microsoft.SqlServer.Management.Smo.Server $SqlServer

# Check if connection succeeded
if (-not $SqlServerConf)
{
  Throw  [System.Exception] "Failed to connect to SQL server $SqlServer "
}
else
{
   Write-Host "Connected to SQL server" $SqlServer
}

# Specify which database to use for the query 
$SqlDatabaseConf = $SqlServerConf.Databases.Item($SqlDatabase)
# Specify query to get parameters out of helper table
$SqlQueryConf = "SELECT Datatype, ParameterName, ParameterValue, ParameterDescription, Sensitive FROM EnvironmentVariables"
# Execute query an put the result in a table object 
$TableConf = $SqlDatabaseConf.ExecuteWithResults($SqlQueryConf).Tables[0]

Write-Host "Found" $TableConf.Rows.Count "variables in config table"
 

############################
########## SERVER ##########
############################
# Load the Integration Services Assembly
Write-Host "Connecting to SSIS server $SsisServer "
$SsisNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
[System.Reflection.Assembly]::LoadWithPartialName($SsisNamespace) | Out-Null;

# Create a connection to the server
$SqlConnectionstring = "Data Source=" + $SsisServer + ";Initial Catalog=master;Integrated Security=SSPI;"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnectionstring

# Create the Integration Services object
$IntegrationServices = New-Object $SsisNamespace".IntegrationServices" $SqlConnection

# Check if connection succeeded
if (-not $IntegrationServices)
{
  Throw  [System.Exception] "Failed to connect to SSIS server $SsisServer "
}
else
{
   Write-Host "Connected to SSIS server" $SsisServer
}


#############################
########## CATALOG ##########
#############################
# Create object for SSISDB Catalog
$Catalog = $IntegrationServices.Catalogs["SSISDB"]

# Check if the SSISDB Catalog exists
if (-not $Catalog)
{
    # Catalog doesn't exists. The user should create it manually.
    # It is possible to create it, but that shouldn't be part of
    # deployment of packages or environments
    Throw  [System.Exception] "SSISDB catalog doesn't exist. Create it manually!"
}
else
{
    Write-Host "Catalog SSISDB found"
}


############################
########## FOLDER ##########
############################
# Create object to the (new) folder
$Folder = $Catalog.Folders[$EnvironmentFolderName]

# Check if folder already exists
if (-not $Folder)
{
    # Folder doesn't exists, so create the new folder.
    Write-Host "Creating new folder" $EnvironmentFolderName
    $Folder = New-Object $SsisNamespace".CatalogFolder" ($Catalog, $EnvironmentFolderName, $EnvironmentFolderName)
    $Folder.Create()
}
else
{
    Write-Host "Folder" $EnvironmentFolderName "found"
}


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

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

    $Environment = New-Object $SsisNamespace".EnvironmentInfo" ($Folder, $EnvironmentName, $EnvironmentName)
    $Environment.Create()
}
else
{
    Write-Host "Environment" $EnvironmentName "found with" $Environment.Variables.Count "existing variables"
    # Recreate to delete all variables, but be careful:
    # This could be harmful for existing references between vars and pars
    # if a used variable is deleted and not recreated.
    #$Environment.Drop()
    #$Environment = New-Object $SsisNamespace".EnvironmentInfo" ($folder, $EnvironmentName, $EnvironmentName)
    #$Environment.Create()
}


###############################
########## VARIABLES ##########
###############################
$InsertCount = 0
$UpdateCount = 0


foreach ($Row in $TableConf)
{
    # Get variablename from array and try to find it in the environment
    $Variable = $Catalog.Folders[$EnvironmentFolderName].Environments[$EnvironmentName].Variables[$Row.Item("ParameterName")]

    # Check if the variable exists
    if (-not $Variable)
    {
        # Insert new variable
        Write-Host "Variable" $Row.Item("ParameterName") "added"
        $Environment.Variables.Add($Row.Item("ParameterName"), $Row.Item("Datatype"), $Row.Item("ParameterValue"), $Row.Item("Sensitive"), $Row.Item("ParameterDescription"))

        $InsertCount = $InsertCount + 1
    }
    else
    {
        # Update existing variable
        Write-Host "Variable" $Row.Item("ParameterName") "updated"
        $Variable.Type = $Row.Item("Datatype")
        $Variable.Value = $Row.Item("ParameterValue")
        $Variable.Description = $Row.Item("ParameterDescription")
        $Variable.Sensitive = $Row.Item("Sensitive")

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


###########################
########## READY ##########
###########################
# Kill connection to SSIS
$IntegrationServices = $null 
Write-Host "Finished, total inserts" $InsertCount " and total updates" $UpdateCount

The table create script and some insert statements to fill the table with sample data.

--TSQL: CreateTableEnvironmentVariables.sql
USE [ssisjoost]
GO

/****** Generate Table [dbo].[EnvironmentVariables]   ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[EnvironmentVariables](
 [Datatype] [varchar](50) NULL,
 [ParameterName] [varchar](50) NULL,
 [ParameterValue] [varchar](255) NULL,
 [ParameterDescription] [varchar](255) NULL,
 [Sensitive] [bit] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO
-- Insert Test data
INSERT [dbo].[EnvironmentVariables] ([Datatype], [ParameterName], [ParameterValue], [ParameterDescription], [Sensitive]) VALUES (N'String', N'MIS_STG_ConnectionString', N'Data Source=.\sql2016;Initial Catalog=MIS_STG;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;', N'Connectionstring to stage database', 0)
GO
INSERT [dbo].[EnvironmentVariables] ([Datatype], [ParameterName], [ParameterValue], [ParameterDescription], [Sensitive]) VALUES (N'String', N'MIS_HST_ConnectionString', N'Data Source=.\sql2016;Initial Catalog=MIS_HST;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;', N'Connectionstring to historical stage database', 0)
GO
INSERT [dbo].[EnvironmentVariables] ([Datatype], [ParameterName], [ParameterValue], [ParameterDescription], [Sensitive]) VALUES (N'String', N'MIS_DWH_ConnectionString', N'Data Source=.\sql2016;Initial Catalog=MIS_DWH;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;', N'Connectionstring to data warehouse database', 0)
GO
INSERT [dbo].[EnvironmentVariables] ([Datatype], [ParameterName], [ParameterValue], [ParameterDescription], [Sensitive]) VALUES (N'String', N'MIS_MTA_ConnectionString', N'Data Source=.\sql2016;Initial Catalog=MIS_MTA;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;', N'Connectionstring to metadata database', 0)
GO
INSERT [dbo].[EnvironmentVariables] ([Datatype], [ParameterName], [ParameterValue], [ParameterDescription], [Sensitive]) VALUES (N'String', N'MIS_DM_ConnectionString', N'Data Source=.\sql2016;Initial Catalog=MIS_DM;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;', N'Connectionstring to data mart database', 0)
GO
INSERT [dbo].[EnvironmentVariables] ([Datatype], [ParameterName], [ParameterValue], [ParameterDescription], [Sensitive]) VALUES (N'String', N'FtpPassword', N'53cr3t!', N'Secret FTP password', 1)
GO
INSERT [dbo].[EnvironmentVariables] ([Datatype], [ParameterName], [ParameterValue], [ParameterDescription], [Sensitive]) VALUES (N'String', N'FtpUser', N'SSISJoost', N'Username for FTP', 0)
GO
INSERT [dbo].[EnvironmentVariables] ([Datatype], [ParameterName], [ParameterValue], [ParameterDescription], [Sensitive]) VALUES (N'String', N'FtpServer', N'ftp://SSISJoost.nl', N'FTP Server', 0)
GO
INSERT [dbo].[EnvironmentVariables] ([Datatype], [ParameterName], [ParameterValue], [ParameterDescription], [Sensitive]) VALUES (N'String', N'FolderStageFiles', N'd:\sources\', N'Location of stage files', 0)
GO
INSERT [dbo].[EnvironmentVariables] ([Datatype], [ParameterName], [ParameterValue], [ParameterDescription], [Sensitive]) VALUES (N'Boolean', N'EnableRetry', N'true', N'Enable retry for Webservice Task', 0)
GO
INSERT [dbo].[EnvironmentVariables] ([Datatype], [ParameterName], [ParameterValue], [ParameterDescription], [Sensitive]) VALUES (N'Int16', N'NumberOfRetries', N'3', N'Number of retries for Webservice Task', 0)
GO
INSERT [dbo].[EnvironmentVariables] ([Datatype], [ParameterName], [ParameterValue], [ParameterDescription], [Sensitive]) VALUES (N'Int16', N'SecsPauseBetweenRetry', N'30', N'Number of seconds between retry', 0)
GO

New environment

Existing environment

Download scripts

Tip: if you keep the parameter names in your SSIS projects equal to the variable names in the environment, then you could automatically references them during deployment using this PowerShell script.

Deploying environments and variables with PowerShell (A: array)

Case
I want to deploy an environment with variables to the Integration Services Catalog. How do I do that?

Environment with variables

Solution
You can't create them outside SQL Server Management Studio and then deploy them to the catalog, because the environment isn't part of the ISPAC file. But it's possible to generate some TSQL code and then execute those Store Procedure calls on all environments of the DTAP.
Or you can use PowerShell script to generate an environment with variables. This blog post shows three different options to use PowerShell to create environments:

1. Store values in (multi-)array
2. Store values in database table
3. Store values in CSV file

A: Store values in (multi-)array
This first example works with a single powershell file with 4 adjustable parameters on top. The variable names and values for this example are stored in an array within the script.

A list of all parameters:

SsisServer contains the servername (and instance name) of the Sql Server that has the Integration Services catalog.

EnvironmentName contains the name of the environment. If the environment already exists then it just adds or updates variables. There are deliberately no deletes. but that's possible.

EnvironmentFolderName contains the foldername where the environment is located. If the folder doesn't exists, it will be created.

EnvironmentVars is an array of variables with the datatype, name, value, description and an indication whether it is a sensitive parameters. The example uses String, Int16 and Boolean, but there are other datatypes possible like DataTime, Int32, Int64, etc. You could store these variables in a database table or an excel sheet and then use some string concatenation to generate the PowerShell code lines for this array.

The script contains a lot of feedback. If it's to excessive for you, you can skip a couple of Write-Host lines by adding a hashtag in front of it. For deploying it's not necessary to change any lines below the parameters.

#PowerShell: GeneralEnvironmentDeploymentWithArray.ps1
################################
########## PARAMETERS ##########
################################  
# Change Server, enviroment folder and enviroment name
$SsisServer = "localhost"
$EnvironmentFolderName = "Environments"
$EnvironmentName = "GenericEnv"

# Change Variables (mind the commas at the end, not on last line)
# Columns: Datatype, Name, Value, Description, Sensitive
$EnvironmentVars = @(
                    ("String","MIS_STG_Connectionstring","Data Source=.\sql2016;Initial Catalog=MIS_STG;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;","Connectionstring to stage database",$false),
                    ("String","MIS_HST_Connectionstring","Data Source=.\sql2016;Initial Catalog=MIS_HST;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;","Connectionstring to historical stage database",$false),
                    ("String","MIS_DWH_Connectionstring","Data Source=.\sql2016;Initial Catalog=MIS_DWH;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;","Connectionstring to data warehouse database",$false),
                    ("String","MIS_MTA_Connectionstring","Data Source=.\sql2016;Initial Catalog=MIS_MTA;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;","Connectionstring to metadata database",$false),
                    ("String","MIS_DM_Connectionstring","Data Source=.\sql2016;Initial Catalog=MIS_DM;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;","Connectionstring to data mart database",$false),
                    ("String","FtpPassword","53cr3t!","Secret FTP password",$true),
                    ("String","FtpUser","SSISJoost","Username for FTP",$false),
                    ("String","FtpServer","ftp://SSISJoost.nl","FTP Server",$false),
                    ("String","FolderStageFiles","d:\sources\","Location of stage files",$false),
                    ("Boolean","EnableRetry",$true,"Enable retry for Webservice Task",$false),
                    ("Int16","NumberOfRetries", 3,"Number of retries for Webservice Task",$false),
                    ("Int16","SecsPauseBetweenRetry", 30,"Number of seconds between retry",$false)
                    )

#################################################
########## DO NOT EDIT BELOW THIS LINE ##########
#################################################
clear
Write-Host "========================================================================================================================================================"
Write-Host "==                                                                 Used parameters                                                                    =="
Write-Host "========================================================================================================================================================"
Write-Host "SSIS Server             :" $SsisServer
Write-Host "Environment Name        :" $EnvironmentName
Write-Host "Environment Folder Path :" $EnvironmentFolderName
Write-Host "Number of Variables     :" $EnvironmentVars.Count
Write-Host "========================================================================================================================================================"


############################
########## SERVER ##########
############################
# Load the Integration Services Assembly
Write-Host "Connecting to server $SsisServer "
$SsisNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
[System.Reflection.Assembly]::LoadWithPartialName($SsisNamespace) | Out-Null;

# Create a connection to the server
$SqlConnectionstring = "Data Source=" + $SsisServer + ";Initial Catalog=master;Integrated Security=SSPI;"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnectionstring

# Create the Integration Services object
$IntegrationServices = New-Object $SsisNamespace".IntegrationServices" $SqlConnection

# Check if connection succeeded
if (-not $IntegrationServices)
{
  Throw  [System.Exception] "Failed to connect to server $SsisServer "
}
else
{
   Write-Host "Connected to server" $SsisServer
}


#############################
########## CATALOG ##########
#############################
# Create object for SSISDB Catalog
$Catalog = $IntegrationServices.Catalogs["SSISDB"]

# Check if the SSISDB Catalog exists
if (-not $Catalog)
{
    # Catalog doesn't exists. The user should create it manually.
    # It is possible to create it, but that shouldn't be part of
    # deployment of packages or environments.
    Throw  [System.Exception] "SSISDB catalog doesn't exist. Create it manually!"
}
else
{
    Write-Host "Catalog SSISDB found"
}


############################
########## FOLDER ##########
############################
# Create object to the (new) folder
$Folder = $Catalog.Folders[$EnvironmentFolderName]

# Check if folder already exists
if (-not $Folder)
{
    # Folder doesn't exists, so create the new folder.
    Write-Host "Creating new folder" $EnvironmentFolderName
    $Folder = New-Object $SsisNamespace".CatalogFolder" ($Catalog, $EnvironmentFolderName, $EnvironmentFolderName)
    $Folder.Create()
}
else
{
    Write-Host "Folder" $EnvironmentFolderName "found"
}


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

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

    $Environment = New-Object $SsisNamespace".EnvironmentInfo" ($Folder, $EnvironmentName, $EnvironmentName)
    $Environment.Create()
}
else
{
    Write-Host "Environment" $EnvironmentName "found with" $Environment.Variables.Count "existing variables"
    # Optional: Recreate to delete all variables, but be careful:
    # This could be harmful for existing references between vars and pars
    # if a used variable is deleted and not recreated.
    #$Environment.Drop()
    #$Environment = New-Object $SsisNamespace".EnvironmentInfo" ($folder, $EnvironmentName, $EnvironmentName)
    #$Environment.Create()
}


###############################
########## VARIABLES ##########
###############################
$InsertCount = 0
$UpdateCount = 0

# Loop through the (multi-)array EnvironmentVars that contains all variables
for($i=0 
    $i -le $EnvironmentVars.Count-1
    $i++)
{
    # Get variablename from array and try to find it in the environment
    $Variable = $Catalog.Folders[$EnvironmentFolderName].Environments[$EnvironmentName].Variables[$EnvironmentVars[$i][1]]

    # Check if the variable exists
    if (-not $Variable)
    {
        # Insert new variable
        Write-Host "Variable" $EnvironmentVars[$i][1] "added"
        $Environment.Variables.Add($EnvironmentVars[$i][1], $EnvironmentVars[$i][0], $EnvironmentVars[$i][2], $EnvironmentVars[$i][4], $EnvironmentVars[$i][3])

        $InsertCount = $InsertCount + 1
    }
    else
    {
        # Update existing variable
        Write-Host "Variable" $EnvironmentVars[$i][1] "updated"
        $Variable.Type = $EnvironmentVars[$i][0]
        $Variable.Value = $EnvironmentVars[$i][2]
        $Variable.Description = $EnvironmentVars[$i][3]
        $Variable.Sensitive = $EnvironmentVars[$i][4]

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


###########################
########## READY ##########
###########################
# Kill connection to SSIS
$IntegrationServices = $null 
Write-Host "Finished, total inserts" $InsertCount " and total updates" $UpdateCount

New environment

Existing environment

Download scripts

Tip: if you keep the parameter names in your SSIS projects equal to the variable names in the environment, then you could automatically references them during deployment using this PowerShell script.

Add footer to Flat File

Case
I have a requirement to add a footer to a flat file with data details, like row count and export date. How do I do that in SSIS?

Flat File with footer text

Solution
If you search the internet you will find several different solutions. Here is a solution with a Script Task.

1) Data Flow Task
I have a standard Data Flow Task with a Row Count Transformation to store the number of records in an integer variable named RowCount and a Flat File Destination to save the data in a textfile. The Connection Manager is named Employee.

DFT with Flat File Destination

2) Footer variable
To keep the .Net code simple and clear I will use an expression on an SSIS string variable to do all the 'difficult stuff'. Add a string variable named Footer and add an expression on it that suits your footer needs. You can make it as complex as you want. In this case a text with the rowcount in it and an export date in a certain format:
"This file contains " +  (DT_WSTR, 6)@[User::RowCount] + " records. Export date: " +
(DT_WSTR, 4)YEAR(GETDATE())  +
RIGHT("0" + (DT_WSTR, 2)MONTH(GETDATE()),2) +
RIGHT("0" + (DT_WSTR, 2)DAY(GETDATE()),2)

Expression with footer text

3) Script Task
Add a Script Task below your Data Flow Task and add the string variable as readonly variable.

Readonly variable: Footer

4) The Script
Add the following using (System.IO) and copy the content of my Main method to yours. It gets the location from your Flat File Connection Manager and appends the content of your footer variable to the bottom. Very basic code and no need to add more code other then error handling.

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

namespace ST_719acd579f7e46adb5d68fb2fdd19625
{
  [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
  public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
  {

    public void Main()
    {
      // Get ConnectionString from Connection Manager (case sensitive)
      string filePath = Dts.Connections["Employee"].AcquireConnection(Dts.Transaction).ToString();

      // Open the file from the connection manager to append some text
      using (StreamWriter sw = File.AppendText(filePath))
      {
        // Append text from string variable to file
        sw.WriteLine(Dts.Variables["User::Footer"].Value.ToString());
      }

      // Close Script Task with success
      Dts.TaskResult = (int)ScriptResults.Success;
    }

    #region ScriptResults declaration
    /// <summary>
    /// This enum provides a convenient shorthand within the scope of this class for setting the
    /// result of the script.
    /// 
    /// This code was generated automatically.
    /// </summary>
    enum ScriptResults
    {
      Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
      Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    };
    #endregion

  }
}

5) The Result
Now run the package and check the result in the Flat File. Not that variable and connection manager names are case sensitive in the script.

Add Footer Script Task

BIML doesn't recognize system variable ServerExecutionID

Case
I want to use the SSIS System Variable ServerExecutionID as a parameter for an Execute SQL Task in a BIML Script, but it doesn't recognize it and gives an error:


Could not resolve reference to 'System.ServerExecutionID' of type 'VariableBase'. 'VariableName="System.ServerExecutionID"' is invalid.

Solution
The current version of BIDS/BIML doesn't recognize all system variables (for example
LocaleId and ServerExecutionID). Other system variables like VersionMajor or VersionBuild will work. You can overcome this by manually adding these variables in your BIML Script.



<Variable Name="ServerExecutionID" DataType="Int64" Namespace="System">0</Variable>

And if you now run the package (in the catalog) the table gets filled with the System variable ServerExecutionID:

Number added, it works!

Value of variable during runtime

Case
I want to know the value of a variable during runtime of an SSIS package.

Value of variable between two tasks

Solution
You can either use a breakpoint or a Script Task to find out the value of a variable.

A) Script Task

A1) Add Script Task
Add a Script Task between the two tasks and select the FilePath variable as readdonly variable.

Script Task - Readonly variable

A2) The script
There are three options that you could choose. Pick one.

//C# code
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_d730d75a40304a6bb675bc184c2aa717
{
 [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
 public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
 {

  public void Main()
  {
        // Choose one of these methods:

        // 1) Fire event and watch the execution result tab
        bool fireAgain = true;
        Dts.Events.FireInformation(-1, "Value of FilePath:", Dts.Variables["User::FilePath"].Value.ToString(), string.Empty, -1, ref fireAgain);


        // 2) Use the .Net framework trace log and see Debug View: http://technet.microsoft.com/en-us/sysinternals/bb896647.aspx
        System.Diagnostics.Trace.WriteLine("Value of FilePath: " + Dts.Variables["User::FilePath"].Value.ToString());


        // 3) Good old messagebox and click to continue
        System.Windows.Forms.MessageBox.Show("Value of FilePath: " + Dts.Variables["User::FilePath"].Value.ToString());


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

        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        /// 
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion
 }
}

or VB.Net

'VB.Net code
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute()> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Public Sub Main()
        ' Choose one of these methods:

        ' 1) Fire event and watch the execution result tab
        Dim fireAgain As Boolean = True
        Dts.Events.FireInformation(-1, "Value of FilePath:", Dts.Variables("User::FilePath").Value.ToString(), String.Empty, -1, fireAgain)


        ' 2) Use the .Net framework trace log and see Debug View: http://technet.microsoft.com/en-us/sysinternals/bb896647.aspx
        System.Diagnostics.Trace.WriteLine("Value of FilePath: " + Dts.Variables("User::FilePath").Value.ToString())


        ' 3) Good old messagebox and click to continue
        System.Windows.Forms.MessageBox.Show("Value of FilePath: " + Dts.Variables("User::FilePath").Value.ToString())


        Dts.TaskResult = ScriptResults.Success
    End Sub

#Region "ScriptResults declaration"
    'This enum provides a convenient shorthand within the scope of this class for setting the
    'result of the script.

    'This code was generated automatically.
    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum

#End Region

End Class

A3) The Result
Now run the package and watch the result.

Oops, no flat file but an Excel file

B) Breakpoints

B1) Breakpoint
Right click the Data Flow Task, Choose "Edit Breakpoints..." and then select the OnPreExecute event. This is the event right before starting the Data Flow Task.

Add breakpoint

B2) Excute package
Now execute the package and wait for it to hit the breakpoint.

Run package and wait for breakpoint

B3) Locals
Wait for the package to hit the breakpoint. Then go to the Debug menu and click Windows and then Locals (Ctrl+Alt+V,L). This wil open a new window.

Locals

B4) The result
Now downdrill the variables in the Locals window and search for your variable and its value.

Oops, no flat file but an Excel file

This last method is probably a lot easier.

Passing variables from a Parent Package To a Child - part II

Case
A few months ago I did a post on how to pass the value of a parent package to a child package. That solution consisted on a peace of .Net coding in a Script Task. But there is an other solution for those who don't like .Net coding.

Solution
This solution consists of using the package configuration.

1) Create parent variable
Create a variable in your parent package. Right click in the Control Flow if the variables are not visible. I used a string variable named FilePath in this test case to store some filepath.

Create new variable, think about the scope.

2) Create Execute Package Task
Drag an Execute Package Task in your Control Flow and configure it to start your child package.

Execute Package Task

3) Create child variable
Goto to your child package and create a variable with the same name as your parent package variable.

Same name!

4) Add configurations
In the SSIS menu choose Package Configurations.

Package Configurations

5) Configuration Type
Enable package configuration(1) and add a configuration. Choose Parent package variable(2) and type the name of the variable(3).

Parent package variable

6) Select Target Property
After you typed the name of the variable, hit the next button and select the value property of the child package variable. After that hit Next, Finish and OK to complete the configuration.

Value property of the child package variable

7) Testing
To test the package I added a simple Script Task (sorry for .net coding) with a messagebox to show the value of the parent package.

Little bit of coding, but just for the show.

The SSIS solution can be downloaded here.

Create a Row Id

Case
If you add records to a database table with an ID column, you preferably would use an identity column (Identity Specification, Is Identity = Yes. But what if your destination does not support an auto-identity or you're not allowed to modify it? You could do this with our Rownumber Component or a Third Party component, or..

Solution
You can use a Script component to accomplish an auto-identity column. In this example I will get the highest ID from a table and use that number as a starting number for new records.

1) Add variable
Add an integer variable named Counter to store an ID.

Right click in Control or Data Flow to show variables

2) Add Execute SQL Task and Data Flow Task
Add an Execute SQL Task to your Control Flow and add a Data Flow Task right behind it.

Execute SQL Task

3) Get max ID
Edit the SQL task and change the ResultSet from None to Single Row. Select the right Connection and enter the query to get the highest ID.

General Tab of  the Execute SQL Task

-- Get highest ID
SELECT  MAX([Id]) as MaxId
FROM    [YourTable]

4) Result Set
Continue editing the SQL task and go to the Result Set tab. Connect the field MaxId from the query to your variable Counter. After this the Execute SQL Task is ready.

Result Set

5) DataFlow
Now go to your Data Flow. Add a random source, a Script Component (transformation) and a destination (the same table as in your Execute SQL Task. Give them suitable names. The result should look something like this.

Data Flow

6) The script Component
We need a new column to store the RowId in. Add a new column on the tab Inputs and Outputs. The type should be an integer, size depends on the column size in your database table.

New column RowId

7) The script itself
SSIS create 3 methods for you: PreExecute to get the MaxId from the variable, Input0_ProcessInputRow to fill the new column RowId and optional PostExecute to fill the variable with the new MaxId after all the records have passed. This third method is only required if your need that number somewhere else.

// C# code: surrogate key script
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    // New internal variable to store the rownumber
    private int rowCounter = 0;

    // Method that will be started before the rows start to pass
    public override void PreExecute()
    {
        base.PreExecute();

        // Lock variable for read 
        VariableDispenser variableDispenser = (VariableDispenser)this.VariableDispenser;
        variableDispenser.LockForRead("User::Counter");
    
        IDTSVariables100 vars;
        variableDispenser.GetVariables(out vars);

        // Fill the internal variable with the value of the SSIS variable
        rowCounter = (int)vars["User::Counter"].Value;

        // Unlock variable
        vars.Unlock();
    }

    // Method that will be started after all rows have passed
    // This method is optional. Only add it if you are gonna
    // use the SSIS variable after the dataflow is finished.
    public override void PostExecute()
    {
        base.PostExecute();

        // Lock variable for write
        VariableDispenser variableDispenser = (VariableDispenser)this.VariableDispenser;
        variableDispenser.LockForWrite("User::Counter");

        IDTSVariables100 vars;
        variableDispenser.GetVariables(out vars);

        // Fill the SSIS variable with the value of the internal variable
        vars["User::Counter"].Value = rowCounter;

        // Unlock variable
        vars.Unlock();
    }

    // Method that will be started for each record in you dataflow  
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        // Seed counter
        rowCounter++;
        // Fill the new column
        Row.RowId = rowCounter;
    }
}

Or VB.net

' VB.Net code: surrogate key script 
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _
<CLSCompliant(False)> _
Public Class ScriptMain
    Inherits UserComponent

    ' New internal variable to store the rownumber
    Private rowCounter As Integer = 0

    ' Method that will be started before the rows start to pass 
    Public Overrides Sub PreExecute()
        MyBase.PreExecute()

        ' Lock variable for read  
        Dim variableDispenser As VariableDispenser = CType(Me.VariableDispenser, VariableDispenser)
        variableDispenser.LockForRead("User::Counter")

        'Use IDTSVariables90 if you're using SSIS 2005
        Dim vars As IDTSVariables100
        variableDispenser.GetVariables(vars)

        ' Fill the internal variable with the value of the SSIS variable
        rowCounter = CInt(vars("User::Counter").Value)

        ' Unlock(Variable)
        vars.Unlock()
    End Sub

    ' Method that will be started after all rows have passed
    ' This method is optional. Only add it if you are gonna
    ' use the SSIS variable after the dataflow is finished.
    Public Overrides Sub PostExecute()
        MyBase.PostExecute()

        ' Lock variable for write 
        Dim variableDispenser As VariableDispenser = CType(Me.VariableDispenser, VariableDispenser)
        VariableDispenser.LockForWrite("User::Counter")

        'Use IDTSVariables90 if you're using SSIS 2005
        Dim vars As IDTSVariables100
        variableDispenser.GetVariables(vars)

        ' Fill the SSIS variable with the value of the internal variable 
        vars("User::Counter").Value = rowCounter
        ' Unlock variable 
        vars.Unlock()
    End Sub

    ' Method that will be started for each record in you dataflow   
    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        'Seed counter
        rowCounter = rowCounter + 1
        ' Fill the new column
        Row.RowId = rowCounter
    End Sub
End Class

8) Map in Destination
Make sure you don't forget to map the new column RowId in your destination. Now run your package to see the result.

An other option to create an unique RowId is to use a GUID instead of an integer. The Script component needs only one method for this solution.

// C# code
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    // Create a Globally Unique Identifier with SSIS
    Row.Guid = System.Guid.NewGuid();
}

' VB.Net code
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
    ' Create a Globally Unique Identifier with SSIS
    Row.Guid = System.Guid.NewGuid()
End Sub

Detailed information about that can be found here.