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?

Answer 1

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