Execute SSIS packages with C#

SQL Server Integration Services packages can be executed  programmatic. In this tutorial I will be showing you how to do it with C#.    I will also explain how to avoid the Error “The Execute method on the task returned error code 0x80131621″.

Reference

In your project you will need to refrence the Microsoft.SqlServer.ManagedDTS assembly.  You should be able to find it some where in your Microsoft SQL Server directorys.

C:Program Files (x86)Microsoft SQL Server110SDKAssembliesMicrosoft.SQLServer.ManagedDTS.dll

Using

Add the following using to your project.

using Microsoft.SqlServer.Dts.Runtime;

App.Config

Because the dll is some kind of mixed mode compiled dll you will need to add something to the app config.

<configuration>
<startup useLegacyV2RuntimeActivationPolicy=”true”>
<supportedRuntime version=”v4.0″ sku=”.NETFramework,Version=v4.0″/>
</startup>
</configuration>

You need to add the useLegacyV2RuntimeActivationPolicy=”true”.   Without it you will see the following error message when you run your application.

The Execute method on the task returned error code 0x80131621 (Mixed mode assembly is built against version ‘v2.0.50727’ of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information.). The Execute method must succeed, and indicate the result using an “out” parameter.

Event Listner

If there is an problem when the package is run its a good idea to have an event listener that can tell you about any problems.

class MyEventListener : DefaultEvents
 {
 public override bool OnError(DtsObject source, int errorCode, string subComponent,
        string description, string helpFile, int helpContext, string idofInterfaceWithError)
       {
       // Output Error Message
       Console.WriteLine("Error in {0}/{1} : {2}", source, subComponent, description);
       return false;
       }
  }

Execute Package

  string mypackage = @"K:DevelopmentVaraiblesVaraiblesPackage.dtsx";
            Package pkg;
            Application app;
            DTSExecResult pkgResults;
            MyEventListener eventListener = new MyEventListener();
            app = new Application();
            pkg = app.LoadPackage(mypackage, eventListener);
            pkgResults = pkg.Execute(null, null, eventListener, null, null);
            Console.WriteLine(pkgResults.ToString());

app.LoadPackage is where all the magic takes place. It loads the package with our eventListener. Then the package is executed.

Notes from author

I realize that this post isn’t as long as some of my other posts.  But you should now understand how to execute a SSIS package programmatic with C#, and how to avoid the Error “The Execute method on the task returned error code 0x80131621″.