Executing SSIS packages from the SSIS catalog on SQL Server 2012

Question

With the new SSIS catalog of SQL Server 2012, the previous way of executing SSIS packages from an SQL Server via C# locally (it basically downloads the package and executes it on the callers machine) does no longer work:

Application app = new Application();
Package pkg = app.LoadFromSqlServer("\\FolderRoot", "myserver", null, null, null);
pkg.Execute();

This approach is discussed in the MSDN article Loading and Running a Local Package Programmatically.

For SQL Server 2012, using the SSIS catalog approach, it seems the new way of executing SSIS packages is by using the classes in the Microsoft.SqlServer.Management.IntegrationServices namespace. Much to my confusion, Microsoft doesn't provide any useful documentation for this new way to handle package execution. The following blog post suggests the following way to do it:

SqlConnection ssisConnection = new SqlConnection(@"Data Source=.\SQL2012;Initial Catalog=master;Integrated Security=SSPI;");
IntegrationServices ssisServer = new IntegrationServices(ssisConnection);
PackageInfo ssisPackage = ssisServer.Catalogs["SSISDB"].Folders["MasterChild"].Projects["MasterChildPackages"].Packages["master.dtsx"];
long executionIdentifier = ssisPackage.Execute(false, null, executionParameter);

However, this does not execute locally, it runs on the server. My question is, with the SSIS catalog on a remote SQL Server 2012, is it still possible to execute packages locally on my own machine from that remote server?

I have never tried executing a package from the SSISDB programmatically. However, a package in the SSIDB is not just a single stand-alone package. It is part of a project. The package has to be executed in the context of the project which can only be done through the SSIDB. — JodyT
That's true, the package needs to execute in the context of it's project. Therefore, the real question is how do I execute an entire remotely deployed project locally on my PC? — soren.qvist
using Microsoft.SqlServer.Dts.Runtime; have you tried this namespace..you want to execute this in c# code right? — Govind
@Govind Yes I want to execute it in C#. That namespace does not work for SQL server where the packages are stored in the SSIS catalog. — soren.qvist

Erti-Chris Eelmaa Erti-Chris Eelmaa · Accepted Answer · 2014-07-30T13:08:24

Define "does not work". As I can see from the documentation, the same code you gave(the one that works for < 2012) SHOULD still work with 2012, it is not marked as deprecated, or anything like that.

Make sure you reference this dll, instead of old one:

C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.ManagedDTS.dll

//edit; it seems that you are indeed right for now: http://technet.microsoft.com/en-us/library/ms141708.aspx

Perhaps this will get you going(ps I have no idea if this will work, but I saw some useful methods, and perhaps you can get it working):

var projectBytes = ssisServer.Catalogs["SSISDB"]
                             .Folders["MasterChild"]
                             .Projects["MasterChildPackages"].GetProjectBytes();

// note that projectBytes is basically __URFILE__.ispac      
using (var existingProject = Project.OpenProject(new MemoryStream(projectBytes)))
{
    existingProject.PackageItems["master.dtsx"].Package.Execute(.... todo ....)
}

Executing SSIS packages from the SSIS catalog on SQL Server 2012

1 Answers