Friday, 28 September 2012

Call SSIS 2012 Package within .Net application - Part 1

Case
I want to start a SSIS 2012 package from a .Net application. How do you do that?

Solution
For this example I will use Visual Studio 2010 (same as for SSIS 2012) and a C# Windows Form Application for this example. I will add the VB.Net code as well.

Note: this example cannot handle (project) parameters that are available in 2012. See this example for project deployed packages.

1) Create project
Start Visual Studio 2010 and create a new C# Windows Form Application project.
C# Windows Form Application


















2) Change framework version
Changed the framework version to 4 in the newley created project. Right click the project and select properties. A new window will open and there you can change the Target framework to 4. Confirm the question about changing the framework version
Changing framework version


















3) Adding reference to SSIS
We need a reference to Microsoft.SqlServer.ManagedDts.dll to call a package. Right click References in the Solution Explorer and click "Add Reference".  Now browse to the 2012 version of ManagedDts.dll. See screendump for location.
Add reference to SSIS


















4) Edit app.Config
Because apparently this new referenced dll is some kind of mixed mode compiled dll, you need to add useLegacyV2RuntimeActivationPolicy="true" to the app.Config. If you don't do this you will get the following error in SSIS:
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.
app.Config


















5) Hello World form
I created a very simple form with a start button (btnStart) and a label (lblStatus) to show the execution result.
My Windows application


















6) Code for start button
This is the code for the start button.
//C# code
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.SqlServer.Dts.Runtime;  // Added

namespace CallSSIS
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btnStart_Click(object sender, EventArgs e)
        {
            // Instantiate SSIS application object
            Microsoft.SqlServer.Dts.Runtime.Application myApplication = new Microsoft.SqlServer.Dts.Runtime.Application();

            // Load package from file system (use LoadFromSqlServer for SQL Server based packages)
            lblStatus.Text = "Loading package from file system.";
            Package myPackage = myApplication.LoadPackage(@"D:\SSIS\MyPackage.dtsx", null);

            // Optional set the value from one of the SSIS package variables
            myPackage.Variables["User::myVar"].Value = "test123";


            // Execute package
            lblStatus.Text = "Executing package";
            DTSExecResult myResult = myPackage.Execute();

            // Show the execution result
            lblStatus.Text = "Package result: " + myResult.ToString();
        }
    }
}


'VB.Net code
Imports Microsoft.SqlServer.Dts.Runtime

Public Class Form1

    Private Sub btnStart_Click(sender As System.Object, e As System.EventArgs) Handles btnStart.Click
        ' Instantiate SSIS application object
        Dim myApplication As New Microsoft.SqlServer.Dts.Runtime.Application()

        ' Load package from file system (use LoadFromSqlServer for SQL Server based packages)
        lblStatus.Text = "Loading package from file system."
        Dim myPackage As Package = myApplication.LoadPackage("D:\SSIS\MyPackage.dtsx", Nothing)

        ' Optional set the value from one of the SSIS package variables
        myPackage.Variables("User::myVar").Value = "test123"

        ' Execute package
        lblStatus.Text = "Executing package"
        Dim myResult As DTSExecResult = myPackage.Execute()

        ' Show the execution result
        lblStatus.Text = "Package result: " & myResult.ToString()
    End Sub
End Class

The result
Build and run the application.

14 comments:

  1. In my case I have two packages and I am calling Master package from .net web application and the master package calls a child package, the master package is working fine when call it from .NET but the child package is throwing following error. I have a Execute SQL Task in which I am referencing a variable in the Expression as SQLStatementSource.

    Error:
    "The result of the expression "SSIS Variable" on property \"SqlStatementSource\" cannot be written to the property. The expression was evaluated, but cannot be set on the property.\r\n"

    ReplyDelete
    Replies
    1. try the DelayValidation option or post a post a question in the SSIS MSDN forum. You have a better change to get a quick answer over there...

      Delete
  2. Why on earth does the SQL 2012 version of Microsoft.SqlServer.ManagedDts.dll need the app.config line for the useLegacyV2RuntimeActivationPolicy="true"?

    That might be the dumbest thing Microsoft has ever done?

    So am I to believe then that SQL 2012 is partially built against some sort of .NET V2 assembly in mind?

    ReplyDelete
    Replies
    1. Not sure, perhaps because this is the old-fashioned way of calling a (single) package. There is a different way of starting packages for the new project deployment. Will publish that later on...

      Delete
  3. Hopefully, this post is still active. I created this project with a DTS package that works in VS 2012 with SSIS. However when I run CallSSIS, I get the following error about not being able to load DTSRuntimewrap:

    System.IO.FileNotFoundException was unhandled
    HResult=-2147024894
    Message=Could not load file or assembly 'Microsoft.SqlServer.DTSRuntimeWrap, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.
    Source=Microsoft.SqlServer.ManagedDTS
    FileName=Microsoft.SqlServer.DTSRuntimeWrap, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
    FusionLog==== Pre-bind state information ===
    LOG: User = FOREST\jenmccarthy
    LOG: DisplayName = Microsoft.SqlServer.DTSRuntimeWrap, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
    (Fully-specified)
    LOG: Appbase = file:///u:/visual studio 2012/Projects/CallSSIS/CallSSIS/bin/Debug/
    LOG: Initial PrivatePath = NULL
    Calling assembly : Microsoft.SqlServer.ManagedDTS, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91.
    ===
    LOG: This bind starts in default load context.
    LOG: Using application configuration file: u:\visual studio 2012\Projects\CallSSIS\CallSSIS\bin\Debug\CallSSIS.vshost.exe.Config
    LOG: Using host configuration file:
    LOG: Using machine configuration file from C:\Windows\Microsoft.NET\Framework64\v4.0.30319\config\machine.config.
    LOG: The same bind was seen before, and was failed with hr = 0x80070002.

    StackTrace:
    at Microsoft.SqlServer.Dts.Runtime.Application..ctor()
    at CallSSIS.Form1.button1_Click(Object sender, EventArgs e) in u:\Visual Studio 2012\Projects\CallSSIS\CallSSIS\Form1.cs:line 24
    at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
    at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
    at System.Windows.Forms.Control.WndProc(Message& m)
    at System.Windows.Forms.ButtonBase.WndProc(Message& m)
    at System.Windows.Forms.Button.WndProc(Message& m)
    at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
    at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
    at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
    at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
    at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
    at CallSSIS.Program.Main() in u:\Visual Studio 2012\Projects\CallSSIS\CallSSIS\Program.cs:line 19
    at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
    at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
    at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
    at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
    at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
    at System.Threading.ThreadHelper.ThreadStart()
    InnerException:

    I'm not sure if this matters, but the version of the DTSRuntimeWrap.dll is 11.0.03369.0. Do I need to downgrade? If so, how?

    ReplyDelete
    Replies
    1. Is SSIS installed on the machine where the .Net application runs?

      Delete
    2. Hi Joost,

      We are getting the same error. SSIS not installed on my machine.

      Please let me know what can be the issue.

      Thanks,
      Ramesh

      Delete
    3. If SSIS is not installed then this approach wont work. Alternatives are to remotely execute a SQL job, Stored Procedure or webservice on the SSIS machine that executes its local package.

      Delete
  4. Hi,
    I have a 4.5 .NET framework class libray that implement different methods to handling SSIS Packages.
    When I try to use methods like folder.Alter(); i get the "Mixed mode assembly is built against version 'v2.0.50727' ..." error. I try to configure App.config file as you suggest but i still get the error.
    Any idea?
    Thanks

    ReplyDelete
  5. Adding to app.config works for me. Thank you very much!

    ReplyDelete
  6. Thanks for this article. That app.config magic was the incantation that let me get on with the task I'm working on.

    ReplyDelete
  7. thanks man, by far the best article and i found several that said "it can't be done". nice

    ReplyDelete

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...