Case: You’ve developed an SSIS project and packages and deployed it to the Catalog using the Project Deployment Model. You need to be able to synchronously/asynchronously execute these packages programmatically from an application you’ve built in .net. How is this done?
I’ll step through this by building a .net console application that will execute an SSIS package.
1) Create a Console Application project
Start Visual Studio 201x and create a new C# console application.
2) Connect to your SQL Server instance
The first thing we need to do is connect to SQL Server. We do this using the SqlConnection class:
using System.Data.SqlClient;
….
//Establish a connection to the SSIS server
SqlConnection sqlConnection = new
SqlConnection(@”Data Source=(local);Initial Catalog=master;Integrated Security=SSPI;”);
NB You’ll likely need to update the Data Source to reflect your server.
3) Connect to SSIS with the IntegrationServices class
//Connect to the SSIS server
IntegrationServices integrationServices = new
IntegrationServices(sqlConnection);
At this point you’re probably seeing a long red squiggle indicating that we’re missing an assembly reference so let’s add it…
4) Add an assembly reference to Microsoft.SqlServer.Management.IntegrationServices.dll
Right-click “References” in the Solution Explorer and choose “Add Reference…”. Click “Browse” and browse to the following location:
C:WindowsassemblyGAC_MSILMicrosoft.SqlServer.Management.IntegrationServices11.0.0.0__89845dcd8080cc91 Microsoft.SqlServer.Management.IntegrationServices.dll
Can’t find it? You’ll need to log on to a computer with SSIS installed and look for the dll in the same location. Copy it to a convenient location on your development server and use the Global Assembly Cache Tool gacutil /i Microsoft.SqlServer.Management.IntegrationServices.dll in a command prompt. gacutil should be installed with Visual Studio (see http://msdn.microsoft.com/en-us/library/ex0ss12c(v=vs.110).aspx).
Add another using directive:
using Microsoft.SqlServer.Management.IntegrationServices;
Once you’ve added the assembly and the using directive the red squiggles should disappear. At this stage you might try building your project and notice a whole bunch of dependent references that need to be added:
- Microsoft.SqlServer.Management.Sdk.Sfc.dll
- Microsoft.SqlServer.Management.Smo.dll
- Microsoft.SqlServer.ConnectionInfo.dll
You can add these dlls using a process similar to the way you added Microsoft.SqlServer.Management.IntegrationServices.dll. The screenshot below shows the locations:
5) Get a handle on a package you want to execute.
A handle on a package is obtained through the Catalogs, Folders, Projects and Packages collections as follows:
PackageInfo myPackage = integrationServices.Catalogs[“SSISDB”].Folders[“MyFolder”].Projects[“MyProject”].Packages[“MyPackage.dtsx”];
The API reference is available at http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.integrationservices.aspx.
6) Set package parameters and SSIS environment.
The PackageInfo.Execute method is overloaded with the definition using all method parameters as follows:
public long Execute(
bool use32RuntimeOn64,
EnvironmentReference reference,
Collection<PackageInfo..::.ExecutionValueParameterSet> setValueParameters,
Collection<PackageInfo..::.PropertyOverrideParameterSet> propetyOverrideParameters
)
Set parameter values on the execution by adding to a collection:
System.Collections.ObjectModel.Collection<PackageInfo.ExecutionValueParameterSet> executionValueParameterSet;
executionValueParameterSet.Add(new
PackageInfo.ExecutionValueParameterSet { ParameterName = “myStringParameter1”, ParameterValue = “myStringParameter1Value”, ObjectType = 30 });
executionValueParameterSet.Add(new
PackageInfo.ExecutionValueParameterSet { ParameterName = “myStringParameter2”, ParameterValue = “myStringParameter2Value”, ObjectType = 30 });
…
ObjectType = 30 is used for parameters and 50 for package properties such as LOGGING_LEVEL and SYNCHRONIZED which we’ll cover below.
Environments are set from the project object (obtained through the PackageInfo.Parent property) as follows:
EnvironmentReference environmentReference = myPackage.Parent.References[“myEnvironmentName”, “environmentFolderName”];
If you don’t need to provide an environment reference you can pass in null as the execute method parameter value.
7) Execute the package
Call the Execute method of the PackageInfo class to execute the package as follows:
long executionIdentifier = myPackage.Execute(false, environmentReference, executionValueParameterSet);
8) Synchronous and Asynchronous Execution
By default, execution is asynchronous. This can be made synchronous by setting the SYNCHRONIZED parameter:
System.Collections.ObjectModel.Collection<PackageInfo.ExecutionValueParameterSet> executionValueParameterSet;
executionValueParameterSet.Add(new
PackageInfo.ExecutionValueParameterSet { ParameterName = “SYNCHRONIZED”, ParameterValue = 1, ObjectType = 50 });
The package is then executed by passing the execution parameters to the Execute method. For example:
myPackage.Execute(true, null, executionValueParameterSet);
Unfortunately if your SSIS package takes longer than 30 seconds to execute you’ll find that the call to the Execute method will time out and the package will fail. At the time of writing there is no straightforward way to adjust the timeout setting. A simple/quick workaround to this issue can be achieved by making an asynchronous call and adding a loop to check for completion of the package after a certain time period (every 5000 milliseconds in this example):
ExecutionOperation executionOperation = catalogSSISDB.Executions[executionIdentifier];
while (!executionOperation.Completed)
{
System.Threading.Thread.Sleep(5000);
executionOperation.Refresh();
}
9) Checking completion status
Finally, you can check completion status of the package with the following:
if (executionOperation.Status == Operation.ServerOperationStatus.Success)
{
Console.WriteLine(“Success”);
}
else
if (executionOperation.Status == Operation.ServerOperationStatus.Failed)
{
Console.WriteLine(“Failed”));
}
else
{
Console.WriteLine(“Something else…”));
}
