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 *

Post a Comment

Previous Post Next Post