Monday, 28 January 2013

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.

Sunday, 20 January 2013

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.

2) Change framework versionChanged 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.dllC:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.Sdk.Sfc\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.Sdk.Sfc.dllC:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dllC:\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

Tuesday, 1 January 2013

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".

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.

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".

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.

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 based1) 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.