Execute SSIS packages with C#


Please Share

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″.

Please Share


Linda Lawton

About Linda Lawton

My name is Linda Lawton I have more than 20 years experience working as a developer and a database expert. I have been working with several of the Google APIs, since 2012. I helping others in the On-Line community to develop with the Google APIs, by creating my own blog www.daimto.com. This and my presence on a number of On-Line developer forums lead me to be noticed by the Google Analytics API development team. I was nominated for and recently became one of the first Google Developer Experts for Google Analytics.

Leave a comment

Your email address will not be published. Required fields are marked *