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.
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.
ReplyDeleteError:
"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"
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...
DeleteWhy on earth does the SQL 2012 version of Microsoft.SqlServer.ManagedDts.dll need the app.config line for the useLegacyV2RuntimeActivationPolicy="true"?
ReplyDeleteThat 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?
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...
DeleteHopefully, 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:
ReplyDeleteSystem.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?
Is SSIS installed on the machine where the .Net application runs?
DeleteHi Joost,
DeleteWe are getting the same error. SSIS not installed on my machine.
Please let me know what can be the issue.
Thanks,
Ramesh
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.
DeleteHi,
ReplyDeleteI 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
Hi Alessio,
DeleteSorry, no suggestions... try the MSDN SSIS forum.
Adding to app.config works for me. Thank you very much!
ReplyDeleteThanks for this article. That app.config magic was the incantation that let me get on with the task I'm working on.
ReplyDeletethanks man, by far the best article and i found several that said "it can't be done". nice
ReplyDeleteThanks for this tuto
ReplyDelete