How do I run an SSIS package in SqlServer 2012 using vs2010?

Question

Setup

I have a vs2010 SSIS package that I can run from visual studio. I have deployed it to my local 2012 server, and I can also execute that package from SSMS. In SSMS, I see the package here:

\Integration Services Catalogs\SSISDB\DAT_Load\Projects\UploadSYS.dtsx

Note: vs2010 does not give me an option to deploy a package anywhere but in a server, and then only in Integration Services Catalogs. Once in there, the MSDB database does not have an entry in the sysssispackages table.

Previously, it was adequate to bring up SSMS and run the package from there(right-click & execute). Now, I have to execute this from a C# web application. Furthermore, I need to trap progress messages and such through events.

Effort

I was able to determine how to set up the event trapping and I got myself to the point where I should have been able to execute the package from code:

    public DTSExecResult ExecutePackage(string packageName, HttpContextBase context)
    {
        string ppath = ConfigurationManager.AppSettings[packageName + "Package"];
        string pserv = ConfigurationManager.AppSettings[packageName + "Server"];
        string puser = ConfigurationManager.AppSettings[packageName + "User"];
        string ppass = ConfigurationManager.AppSettings[packageName + "Pwd"];
        _context = context;
        _pkgLocation = "";
        _app = new Application();
        _pkg = _app.LoadFromSqlServer(ppath, pserv, puser, ppass, _SSISEvents);
        _pkgResults = _pkg.Execute(_connections, _variables, _SSISEvents, _log, null);
        return _pkgResults;
    }

Problem

I cannot locate the package. When I reach the LoadFromSqlServer statement, I receive an error that says:

Cannot find folder "\Integration Services Catalogs\SSISDB\DAT_Load\Projects\UploadSYS.dtsx"

The same thing happens for variations in the path (variable = ppath):

• \Integration Services Catalogs\SSISDB\DAT_Load\Projects\UploadSYS.dtsx
• \SSISDB\DAT_Load\Projects\UploadSYS.dtsx
• \DAT_Load\Projects\UploadSYS.dtsx
• etc.

Running this from the command line or a stored procedure is not an option.

So, can anyone tell what I am missing here? Does the Application object need to initialize something? Is this even possible?

Answer 1

Taking another bite at the problem but see Set SSIS database package path and SSIS Organization for background reading.

Until SSIS 2012, if packages were deployed to SQL Server, they lived in the msdb. The .NET API for interacting with them was the same across versions.

With the 2012 release of SSIS, we have two different deployment models. Package deployment, which is the "classic" model is alive and fully supported. The same code for running a package on 2005 will work for 2012 package deployment model projects. This is the Microsoft.SqlServer.Dts.Runtime Namespace

Your code is attempting to load a 2012 solution built using the "project deployment model" with the "package deployment model" API. This is the Microsoft.SqlServer.Management.IntegrationServices Namespace and the two don't mix.

Your options are to switch your project back to the Package deployment model or update your code. In the first linked question, I provided the VB.NET implementation for running an SSIS package in the SSISDB catalog. There is some way of running a .ispac file because I see the option in dtexec but I'm not seeing the specific method. This is mechanism VS/SSDT uses when it runs the packages locally.