Multiple Configurations in SSDT

Case
In the SSIS Catalog we have the ability to configure for several environments by creating multiple environments (one for development, one for test), but how do we easily switch environments in SSDT while developing?

Multiple environments

Solution
In SQL Server Data Tools (SSDT) we have the Configuration Manager that can do something similar.


1) Start
Lets start with a basic package which loads a flat file into a database table. The two connection managers should be changed if we want to load data from the Test environment instead of data from the Development environment.

My basic package

2) Package Parameter
We have to create a Package Parameter for the Flat File Connection Manager. (This Connection Manager is for this package only.) Right click the 'Clients' Connection Manager and click Parameterize. Select the ConnectionString as the property. Create a new parameter and set the scope to Package.

Parameterize Connection Manager

3) Project Parameter
Do the same for the OLE DB Connection Manager that connects to SQL Server. Because this is a Project Connection Manager, the Scope is automatically set to Project

Parameterize Connection Manager

4) Add Configuration
Next step is to add a Configuration in addition to the existing Development Configuration. In the Standard toolbar you see a drop down called Solution Configurations. Open it and select Configuration Manager. A new window will open.
In the Active solution configuration drop down click on <New...>. Now you can enter a new name and optional copy settings from the existing Development Configuration.

Adding a Configuration

5) Add Parameters to Configuration - Package Parameter
Now we have two Configurations (Developement and Test) and we can give the parameters different values for each Configuration. First the Package Parameter: Go to the Parameter tab in the package and click on 'Add Parameters to Configuration'.
Next, click on the Add button in the new window to select a Parameter to configure. In the next new window you can select that variable.

Add Parameters to Configuration

Now you have added the Parameter to the Configurations, you can give it a different value for each Configuration.

Change Parameter value for Test Configuration

6) Add Parameters to Configuration - Project Parameter
Now do the same for the Project Parameter. In the Solution Explorer (default upper right corner) you can find the Project Parameters. This will open a new document. The rest is the same as the previous step.

Add Project Parameters to Configuration

Change the parameter value for the Test Configuration.


Change Project Parameter value for Test Configuration

7) Test
Now you can run the package with different Configurations within SSDT. In this example a small file for Development and a large file for Test.

Running with different Configurations

Note 1: These configurations won't be deployed to the SSISDB.
Note 2: You can also change other project properties per Configurations, like the deployment path or the 32/64bit property.

Configure other project properties

SSIS 2012 Data taps

Case
I have a (very basic) package and want to add data taps to it in the Integration Services Catalogs.

My package adding colors to a table

Solution
Datataps are the 'dataviewers' for packages within the Integration Services Catalogs, but their output is to a file instead of to the screen. They can be added with the stored procedures from the SSISDB.

My package in the SSIS Catalog

1) Execution
We first have to create an execution and we need its execution id in the next stored procedure calls. The Folder, Project and Packagename can be found in the picture above.

-- Create a variable to store the ID of the package execution
DECLARE @execution_id bigint

-- Create a package execution and fill the variable
EXECUTE [SSISDB].[catalog].[create_execution] 
  @folder_name = 'ilionx'
  ,@project_name = 'DataTap'
  ,@package_name = 'DimColors.dtsx' 
  ,@reference_id = null
  ,@use32bitruntime = false
  ,@execution_id = @execution_id OUTPUT

-- Add some optional parameters like Verbose logging
EXECUTE [SSISDB].[catalog].[set_execution_parameter_value]
  @execution_id = @execution_id
  ,@object_type=50
  ,@parameter_name=N'LOGGING_LEVEL'
  ,@parameter_value=3 -- Verbose

2) Data taps
Now we have an execution, we can add data taps to it. For this we need to know the PackagePath or GUID of the Data Flow Task you want to tap.


The (GU)ID and PackagePath of the Data Flow Task.

And we need to know the IdentificationString of the Data Flow Path within the Data Flow Task.

Data Flow Path properties

I will add one data tap with the PackagePath and the other on the next Data Flow Path with the GUID.

-- Create a data type with the data flow PackagePath 
EXECUTE [SSISDB].[catalog].[add_data_tap]
  @execution_id = @execution_id
  ,@task_package_path = '\Package\Add Colors'
  ,@dataflow_path_id_string = 'Paths[SRC - Colors.Flat File Source Output]'
  ,@data_filename = 'ssisjoost1.txt'
  ,@max_rows = 10

-- Create a data type with the data flow ID
EXECUTE [SSISDB].[catalog].add_data_tap_by_guid
  @execution_id = @execution_id
  ,@dataflow_task_guid = '{9DE67956-E158-4264-AA98-F9C07A7C7731}'
  ,@dataflow_path_id_string = 'Paths[DER - Uppercase.Derived Column Output]'
  ,@data_filename = 'ssisjoost2.txt'
  ,@max_rows = 10

3) Execute
The last step is to execute the created execution and watch the output folder (C:\Program Files\Microsoft SQL Server\110\DTS\DataDumps) for new files.

-- Execute the created execution
EXECUTE [SSISDB].[catalog].[start_execution]
  @execution_id = @execution_id

Note: For steps 1 and 3 of above you can also use the Script button. Then you only have to add the code from step 2. See steps in this picture:

Call SSIS 2012 Package within .Net application - Part 2

Case
I want to start a SSIS 2012 package from a .Net application, but I want to use project variables and project connection managers. How do you do that?

How to start that Master.dtsx package with .Net

Solution
I while ago I posted an example for the 'old fashioned' way of executing packages with .Net. This example is for the new project deployment packages in the SSISDB.

1) Create project
Start Visual Studio 2010 and create a new C# Windows Form Application project.

Select .NET Framework 4 or do that in step 2

2) Change framework version
Changed the framework version to 4 in the newley created project. Right click the project and select properties. A new window will open and there you can change the Target framework to 4. Confirm the question about changing the framework version

Changing framework version

3) Adding reference to SSIS
We need to reference 4 assemblies, but they are not in the SQL Server folder. They are only available in the GAC. The path varies a little per computer. Here are mine:
C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ConnectionInfo\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.ConnectionInfo.dll
C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.Sdk.Sfc\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.Sdk.Sfc.dll
C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll
C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.IntegrationServices\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.IntegrationServices.dll

Adding four references

4) The form
For this example I added a button and a ListBox to show the log from SSIS.

Example form

5) The code
This is the code for the start button, but don't forget the usings on top.

//C# code
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
// Added:
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.IntegrationServices;
using System.Collections.ObjectModel;

namespace CallSSIS2012Package
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void StartPackageButton_Click(object sender, EventArgs e)
        {
            // Connection to the database server where the packages are located
            SqlConnection ssisConnection = new SqlConnection(@"Data Source=.\SQL2012;Initial Catalog=master;Integrated Security=SSPI;");

            // SSIS server object with connection
            IntegrationServices ssisServer = new IntegrationServices(ssisConnection);

            // The reference to the package which you want to execute
            PackageInfo ssisPackage = ssisServer.Catalogs["SSISDB"].Folders["MasterChild"].Projects["MasterChildPackages"].Packages["master.dtsx"];

            // Add execution parameter to override the default asynchronized execution. If you leave this out the package is executed asynchronized
            Collection<PackageInfo.ExecutionValueParameterSet> executionParameter = new Collection<PackageInfo.ExecutionValueParameterSet>();
            executionParameter.Add(new PackageInfo.ExecutionValueParameterSet { ObjectType = 50, ParameterName = "SYNCHRONIZED", ParameterValue = 1 });

            // Get the identifier of the execution to get the log
            long executionIdentifier = ssisPackage.Execute(false, null, executionParameter);

            // Loop through the log and add the messages to the listbox
            foreach (OperationMessage message in ssisServer.Catalogs["SSISDB"].Executions[executionIdentifier].Messages)
            {
                SSISMessagesListBox.Items.Add(message.MessageType.ToString() + ": " + message.Message);
            }
        }
    }
}

or VB.Net

'VB.Net code
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Management.IntegrationServices
Imports System.Collections.ObjectModel

Public Class Form1

    Private Sub StartPackageButton_Click(sender As System.Object, e As System.EventArgs) Handles StartPackageButton.Click
        ' Connection to the database server where the packages are located
        Dim ssisConnection As New SqlConnection("Data Source=.\SQL2012;Initial Catalog=master;Integrated Security=SSPI;")

        ' SSIS server object with connection
        Dim ssisServer As New IntegrationServices(ssisConnection)

        ' The reference to the package which you want to execute
        Dim ssisPackage As PackageInfo = ssisServer.Catalogs("SSISDB").Folders("MasterChild").Projects("MasterChildPackages").Packages("master.dtsx")

        ' Add execution parameter to override the default asynchronized execution. If you leave this out the package is executed asynchronized
        Dim executionParameters As New Collection(Of PackageInfo.ExecutionValueParameterSet)
        Dim executionParameter As New PackageInfo.ExecutionValueParameterSet
        executionParameter.ObjectType = 50
        executionParameter.ParameterName = "SYNCHRONIZED"
        executionParameter.ParameterValue = 1
        executionParameters.Add(executionParameter)

        ' Get the identifier of the execution to get the log
        Dim executionIdentifier As Long = ssisPackage.Execute(False, Nothing, executionParameters)

        ' Loop through the log and add the messages to the listbox
        For Each message As OperationMessage In ssisServer.Catalogs("SSISDB").Executions(executionIdentifier).Messages
            SSISMessagesListBox.Items.Add(message.MessageType.ToString() + ": " + message.Message)
        Next
    End Sub
End Class

6) The result
Now run the applcation and see the result.

If you want to add parameters then take a look at my SSIS WIKI example.

* update: 30 second timeout workaround *

Master Child packages - Part 3: Project reference

Case
An often seen solution is a master package calling a couple of child packages with the Execute Package Task. This works fine for a couple of packages, , but is a little boring for a whole bunch of packages. Is there an easier more clear way to maintain a master package?

Server based and file based child packages

Solution
A simple solution is to use a Foreach Loop Container with an Execute Package Task in it that loops through a folder with packages. It works both for file-based and server-based packages.

But there are a couple of drawbacks:
Drawback 1: The child packages are not executed simultaneously, but one after another. Will handle this problem in a future post.
Drawback 2: The options to determine the order of execution are limited. You can only order by name. So if a certain order is required then you need to add some prefix to the packagename to determine the order.

I have prepared three solutions:
A) File based: SSIS 2005, 2008 or 2012 if you use package deployment.
B) SQL Server based: SSIS 2005, 2008 or 2012 if you use package deployment.
C) Project Referenced: SSIS 2012 if you use project deployment. This solution is nearly equal to solution B.



C) Project Referenced
For this solution, you need to create a query on the SSISDB database to get the list of packages from Integration Service. For this query we need the tables internal.packages and internal.projects.

Get list of packages from SSISDB in SSIS 2012

1) Variables
Add a string variable to the package and name it PackagePath. This will contain the filepath of the package. Also create an object variable named Packages. This will contain a list of packages from the SSISDB.

Right click in Control Flow



2) OLE DB Connection Manager
Create an OLE DB Connection Manger that connects to the msdb database. We will use this connection manager for geting a list of packages and to execute the SQL Server based packages.

OLE DB Connection to SSISDB



3) Execute SQL Task
Add an Execute SQL Task and give it a suitable name. Edit it; Set ResultSet to Full result set. Select the newly created Connection Manger and enter the query below.

Execute SQL Task



-- Get list of packages. Change the where clause.
SELECT      Packages.[name]
FROM        [SSISDB].[internal].[packages] as Packages
INNER JOIN  [SSISDB].[internal].[projects] as Projects
            on Packages.project_version_lsn = Projects.object_version_lsn
WHERE       Projects.name = 'MasterChildPackages'
AND         Packages.name like 'STG%'
ORDER BY    Packages.name

4) Execute SQL Task - Result Set
Go to the Result Set pane and click Add and select the object variable from step 1. The Result Name should be 0.

Result Set



5) Foreach Loop
Add a Foreach Loop Container to the control flow and give it a suitable name. Then connect the Execute SQL Task to the Foreach Loop.

Foreach Loop Container



6) Foreach ADO Enumerator
Edit the Foreach loop and select the Foreach ADO Enumerator as the enumerator type. After that select the object variable Packages as the ADO object source variable. The Enumeration mode should be "Rows in the first table".

Foreach ADO Enumerator



7) Variable Mapping
Go to the Variable Mapping pane and select the PackagePath variable for index 0.

Variable Mappings



8) Execute Package Task
Add an Execute Package Task in the Foreach Loop. Give it a suitable name and configure it to call one of your child packages (ReferenceType = Project Reference). Just pick one. We will overrull the path in the next step.

Execute Package Task - Project Reference



9) Expression
Go to the properties of your newly created Execute Package Task and add an expression on the PackageName property that overrules its value with the variable PackagePath from step 1.

Expression overrulling PackageName(path)



10) Delay Validation
If the value of the PackagePath variable doesn't contain a real path of a variable, then you will get a validation error on runtime. You could either fill the variable with a default value or just set the Delay Validation property of the Execute Package Task to false.

The package is not specified

11) The result
A clear package with only one Execute Package Task.

The result



Go to A) File based or B) SQL Server based

Master Child packages - Part 2: SQL Server based

Case
An often seen solution is a master package calling a couple of child packages with the Execute Package Task. This works fine for a couple of packages, but is a little boring for a whole bunch of packages. Is there an easier more clear way to maintain a master package?

Server based and file based child packages

Solution
A simple solution is to use a Foreach Loop Container with an Execute Package Task in it that loops through a folder with packages. It works both for file-based and server-based packages.

But there are a couple of drawbacks:
Drawback 1: The child packages are not executed simultaneously, but one after another. Will handle this problem in a future post.
Drawback 2: The options to determine the order of execution are limited. You can only order by name. So if a certain order is required then you need to add some prefix to the packagename to determine the order.

I have prepared three solutions:
A) File based: SSIS 2005, 2008 or 2012 if you use package deployment.
B) SQL Server based: SSIS 2005, 2008 or 2012 if you use package deployment.
C) Project Referenced: SSIS 2012 if you use project deployment. This solution is nearly equal to solution B.


B) SQL Server based
For this solution, you need to create a query on the msdb database to get the list of packages from Integration Service. For this query we need the system tables sysssispackages and sysssispackagefolders.

Get list of packages from MSDB in SSIS 2008

1) Variables
Add a string variable to the package and name it PackagePath. This will contain the filepath of the package. Also create an object variable named Packages. This will contain a list of packages from the MSDB.

Right click in Control Flow

2) OLE DB Connection Manager
Create an OLE DB Connection Manger that connects to the msdb database. We will use this connection manager for geting a list of packages and to execute the SQL Server based packages.

OLE DB Connection to MSDB

3) Execute SQL Task
Add an Execute SQL Task and give it a suitable name. Edit it; Set ResultSet to Full result set. Select the newly created Connection Manger and enter the query below.

Execute SQL Task

-- Get list of packages. Change the where clause.
SELECT   '\' + folders.foldername + '\' + packages.name as PackagePath --'Concatenate
FROM   msdb.dbo.sysssispackages as packages
INNER JOIN  msdb.dbo.sysssispackagefolders as folders
    on folders.folderid = packages.folderid
WHERE   folders.foldername = 'Staging'
AND    packages.name like 'STG%'
ORDER BY  packages.name

4) Execute SQL Task - Result Set
Go to the Result Set pane and click Add and select the object variable from step 1. The Result Name should be 0.

Result Set

5) Foreach Loop
Add a Foreach Loop Container to the control flow and give it a suitable name. Then connect the Execute SQL Task to the Foreach Loop.

Foreach Loop Container

6) Foreach ADO Enumerator
Edit the Foreach loop and select the Foreach ADO Enumerator as the enumerator type. After that select the object variable Packages as the ADO object source variable. The Enumeration mode should be "Rows in the first table".

Foreach ADO Enumerator

7) Variable Mapping
Go to the Variable Mapping pane and select the PackagePath variable for index 0.

Variable Mappings

8) Execute Package Task
Add an Execute Package Task in the Foreach Loop. Give it a suitable name and configure it to call one of your child packages (Location = SQL Server). Just pick one. We will overrull the path in the next step.

Execute Package Task

9) Expression
Go to the properties of your newly created Execute Package Task and add an expression on the PackageName property that overrules its value with the variable PackagePath from step 1.

Expression overrulling PackageName(path)

10) Delay Validation
If the value of the PackagePath variable doesn't contain a real path of a variable, then you will get a validation error on runtime. You could either fill the variable with a default value or just set the Delay Validation property of the Execute Package Task to false.

The package is not specified

11) The result
A clear package with only one Execute Package Task.

The result

Go to A) File based or C) Project Referenced

Master Child packages - Part 1: File based

Case
An often seen solution is a master package calling a couple of child packages with the Execute Package Task. This works fine for a couple of packages, but is a little boring for a whole bunch of packages. Is there an easier more clear way to maintain a master package?

Server based and file based child packages

Solution
A simple solution is to use a Foreach Loop Container with an Execute Package Task in it that loops through a folder with packages. It works both for file-based and server-based packages.

But there are a couple of drawbacks:
Drawback 1: The child packages are not executed simultaneously, but one after another. Will handle this problem in a future post.
Drawback 2: The options to determine the order of execution are limited. You can only order by name. So if a certain order is required then you need to add some prefix to the packagename to determine the order.

I have prepared three solutions:
A) File based: SSIS 2005, 2008 or 2012 if you use package deployment.
B) SQL Server based: SSIS 2005, 2008 or 2012 if you use package deployment.
C) Project Referenced: SSIS 2012 if you use project deployment. This solution is nearly equal to solution B.


A) File based
1) Variable
Add a string variable to the package and name it PackagePath. This will contain the filepath of the package.


Right click in Control Flow

2) Foreach Loop
Add a foreach loop to your master package. Edit it to give it a suitable name and to select the File Enumerator. If you need a certain order then you could install the Sorted File Enumerator.

File Enumerator

3) Path and folder
Enter the folder name where your packages are located and enter a filter (example: STG*.dtsx). Make sure the Fully qualified options is selected.

Loop through package folder

4) Variable Mappings
Go to the variable mappings pane and select the variable from step 1. This will fill the variable with the path of the current package.


Select the String variable from step 1

5) Execute Package Task
Add an Execute Package Task in the Foreach Loop. Give it a suitable name and configure it to call one of your child packages (Location = File system). Just pick one. We will overrull the path in the next step.

Calling a file based package

6) Expression
Go to the properties of your newly created File Connection Manager and add an expression on the ConnectionString property that overrules its value with the variable PackagePath from step 1.

Expression on new connection manager

7) The result
A clear package with only one Execute Package Task and one Connection Manager.

The result

Go to B) SQL Server based or C) Project Referenced