Wednesday 2 January 2013

Call SSIS 2012 Package within .Net application - Part 2

Case
I want to start a SSIS 2012 package from a .Net application, but I want to use project variables and project connection managers. How do you do that?
How to start that Master.dtsx package with .Net




















Solution
I while ago I posted an example for the 'old fashioned' way of executing packages with .Net. This example is for the new project deployment packages in the SSISDB.

1) Create project
Start Visual Studio 2010 and create a new C# Windows Form Application project.
Select .NET Framework 4 or do that in step 2

















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 to reference 4 assemblies, but they are not in the SQL Server folder. They are only available in the GAC. The path varies a little per computer. Here are mine:
C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ConnectionInfo\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.ConnectionInfo.dll
C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.Sdk.Sfc\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.Sdk.Sfc.dll
C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll
C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.IntegrationServices\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.IntegrationServices.dll

Adding four references


















4) The form
For this example I added a button and a ListBox to show the log from SSIS.
Example form




















5) The code
This is the code for the start button, but don't forget the usings on top.
//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;
// Added:
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.IntegrationServices;
using System.Collections.ObjectModel;

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

        private void StartPackageButton_Click(object sender, EventArgs e)
        {
            // Connection to the database server where the packages are located
            SqlConnection ssisConnection = new SqlConnection(@"Data Source=.\SQL2012;Initial Catalog=master;Integrated Security=SSPI;");

            // SSIS server object with connection
            IntegrationServices ssisServer = new IntegrationServices(ssisConnection);

            // The reference to the package which you want to execute
            PackageInfo ssisPackage = ssisServer.Catalogs["SSISDB"].Folders["MasterChild"].Projects["MasterChildPackages"].Packages["master.dtsx"];

            // Add execution parameter to override the default asynchronized execution. If you leave this out the package is executed asynchronized
            Collection<PackageInfo.ExecutionValueParameterSet> executionParameter = new Collection<PackageInfo.ExecutionValueParameterSet>();
            executionParameter.Add(new PackageInfo.ExecutionValueParameterSet { ObjectType = 50, ParameterName = "SYNCHRONIZED", ParameterValue = 1 });

            // Get the identifier of the execution to get the log
            long executionIdentifier = ssisPackage.Execute(false, null, executionParameter);

            // Loop through the log and add the messages to the listbox
            foreach (OperationMessage message in ssisServer.Catalogs["SSISDB"].Executions[executionIdentifier].Messages)
            {
                SSISMessagesListBox.Items.Add(message.MessageType.ToString() + ": " + message.Message);
            }
        }
    }
}

or VB.Net

'VB.Net code
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Management.IntegrationServices
Imports System.Collections.ObjectModel

Public Class Form1

    Private Sub StartPackageButton_Click(sender As System.Object, e As System.EventArgs) Handles StartPackageButton.Click
        ' Connection to the database server where the packages are located
        Dim ssisConnection As New SqlConnection("Data Source=.\SQL2012;Initial Catalog=master;Integrated Security=SSPI;")

        ' SSIS server object with connection
        Dim ssisServer As New IntegrationServices(ssisConnection)

        ' The reference to the package which you want to execute
        Dim ssisPackage As PackageInfo = ssisServer.Catalogs("SSISDB").Folders("MasterChild").Projects("MasterChildPackages").Packages("master.dtsx")

        ' Add execution parameter to override the default asynchronized execution. If you leave this out the package is executed asynchronized
        Dim executionParameters As New Collection(Of PackageInfo.ExecutionValueParameterSet)
        Dim executionParameter As New PackageInfo.ExecutionValueParameterSet
        executionParameter.ObjectType = 50
        executionParameter.ParameterName = "SYNCHRONIZED"
        executionParameter.ParameterValue = 1
        executionParameters.Add(executionParameter)

        ' Get the identifier of the execution to get the log
        Dim executionIdentifier As Long = ssisPackage.Execute(False, Nothing, executionParameters)

        ' Loop through the log and add the messages to the listbox
        For Each message As OperationMessage In ssisServer.Catalogs("SSISDB").Executions(executionIdentifier).Messages
            SSISMessagesListBox.Items.Add(message.MessageType.ToString() + ": " + message.Message)
        Next
    End Sub
End Class

6) The result
Now run the applcation and see the result.




















If you want to add parameters then take a look at my SSIS WIKI example.

* update: 30 second timeout workaround *

27 comments:

  1. Thanks for posting this. I do have a couple questions though...

    What makes the old way 'old fashioned'? Is Microsoft pushing the new project deployment packages and the Integration Services Catalog? What are the advantages to migrating that way versus just staying on the existing way? Do the project variables and project connections have more benefits?

    Any help you could give would be greatly appreciated. Or do you know of any good blogs, articles, books, etc... to migrating from the existing way to the new SSISDB way?

    Thanks!

    ReplyDelete
    Replies
    1. The new default in SSIS 2012 is project deployment instead of package deployment. But you can still use both methods.

      Using parameters and project connection managers is just making things easier for you. If you use the same variable or connection manager in 50 packages it could be a lot of work to maintain... Now you only have one place.

      The best place to ask general ssis questions is the MSDN Forum. And some very good blogs:
      http://sqlblog.com/blogs/jamie_thomson/default.aspx
      http://blogs.msdn.com/b/mattm/
      http://www.mattmasson.com/

      Delete
    2. Basically before SSIS catalogue DB a lot of teams were building their own framework db's for extending, logging, meta data configuration, data lineage and reporting. All added value stuff you really should have in a good etl process. I've seen loads some are good and some are awful and buggy simply because not enough project time was devoted to them. Basically involves building your DB, building custom components or using standard components on events or outside of sequence container wrappers. With the new project deployment some of this now comes as standard out of the box - some still argue their frameworks are better (some are some just don't want to let go!). Also fundamentally with SSIS catalogue and project deployment Configurations and Logging are now isolated on the server and has nothing to do with the development process. i.e. once parameters are developed into the package how their configured in respective deployed environments is totally separated from the development process which simplifies development and deployment and puts into the hands of administrator where it should also true of logging. i.e. if want to change the logging on the deployed project on the server an administrator can do that very simply with server admin tools. You don't have to open the package and change the logging events on the package. Rightly configuration and logging should server administration tasks not development.

      Delete
  2. Well done.
    How would you run this with a package located at D:\SSIS\MyPackage.dtsx?
    Thanks in advance.
    Roby

    ReplyDelete
    Replies
    1. See part 1 for file based packages. Link is on top of this page.

      Delete
    2. Thanks for your prompt reply.
      I am unable to reference 'Microsoft.SqlServer.Dts.Runtime' which is no where on my system(SQL2012 and VS2012)
      Part 2 is exactly what I prefer simulating. Is there a way to point the PackageInfo to a file based package?
      Roby

      Delete
    3. This method only works with packages that are stored in the SSISDB (project based). For packages that are file or msdb based you need a reference to Microsoft.SqlServer.ManagedDts.dll
      See location/details in part 1, step 3

      Delete
  3. Thanks for writing this article. Unfortunately the MSDN documentation of this new Namespace is very poorly written. There are no class overviews, no sample code and for methods that return integer values there is no list of possible return values.

    One question I have is how to set a package variable before execution. In the "Old Fashioned" way I would use package.Variables["MyVariable"].Value = Myvalue. In the new PackageInfo class there is no Variable collection property.

    ReplyDelete
    Replies
    1. Hi Anonymous,

      The MSDN documention is indeed very poor. Will try to add a parameter to this example code in a couple of days.

      Regards,

      Joost

      Delete
    2. Hi Anonymous,


      I added an SSIS WIKI with some example code.

      Delete
    3. Hi Joost,

      I tried to set the package variable as you explained in the code. but I am getting the parameter does not exist or you do not have sufficient permissions. I have integer variable intSchedule in my package and I used the following code to set value to this variable executionParameter.Add(new PackageInfo.ExecutionValueParameterSet { ObjectType = 50, ParameterName = "IntSchedule", ParameterValue = "0" });. Can you please help me on this

      Delete
    4. Hi Sajesh K, for this example you should use a parameter in the package instead of a variable. Otherwise check part 1 for package deployment (this is project deployment)

      Delete
  4. hello,win8x64,vs2012,
    error:
    错误 48 System.IO.FileNotFoundException - 未能加载文件或程序集“file:///C:\windows\Gac_32\Microsoft.SqlServer.DTSRuntimeWrap.dll”或它的某一个依赖项。系统找不到指定的文件。 D:\Working\iResearch\MUT\Source\MUT_Publish_Data_NewAlgorithm\PublishData\TlbImp PublishData

    what can i do ? thank you very much !!

    ReplyDelete
    Replies
    1. Remove that reference. You don't need it. See step 3

      Delete
  5. Hi, Joost, your post was very helpful, is there anyway to pass from your code to ssis package parameter, not variable? In SSIS 2010 there is an option for package parameters, i would like to use it.
    thx.

    ReplyDelete
    Replies
    1. Hi Vladimir,
      try something like:
      executionParameter.Add(new PackageInfo.ExecutionValueParameterSet { ObjectType = 30, ParameterName = "YourStringParam", ParameterValue = "d:\test\bla.txt" });

      Delete
    2. Hi Vladimir,

      I added an SSIS WIKI with some example code.

      Delete
  6. Does the .net code for this have to be executed on the same server as SSIS is stored on? When i did things using the DTS.Runtime namespace on SQL 2008, I was unable to see if a package was completed unless the .net code was executing on the same server as SSIS. Using the new API, can I execute on a different server and get the event raised when the package has completed?

    - Rob

    ReplyDelete
    Replies
    1. Hi Rob,

      You need to execute it on the same server as SSIS. Alternatives are to create SQL Server Agent Jobs and execute them remotely via TSQL in your .Net application... or create a webservice on your SSIS machine and call it remotely via your .Net application.

      Delete
  7. Is it possible to retrieve errors that are also accessible via the Errors property of the Microsoft.SqlServer.Dts.Runtime.Package class?

    ReplyDelete
    Replies
    1. I don't think so if you're executing via the SSISDB. You could post a question in the MSDN forum to be sure...

      Delete
  8. Hello
    I'm using this since nearly a year. Great post!
    Now I've updated my packages to SQL2014 / VS2013 and the component is not working anymore.
    It says:
    "Package migration from version 8 to version 6 failed with error 0xC001700A "The version number in the package is not valid. The version number cannot be greater than current version number."

    What can I do?
    Thanks in advance
    Richard

    ReplyDelete
    Replies
    1. Have you changed the references from step 3? Each version of SSIS has it's own assemblies,

      Delete
  9. Hi Joost thank you for the post. This is great for people like me who have no real clue about .NET and give us something to go with. I was just wondering how I could loop through and get a list of the SSIS packages in Project (deployed to the 2012 SSIS catalogue)

    ReplyDelete
    Replies
    1. I think you should try something like:
      foreach (PackageInfo myPackage in ssisServer.Catalogs["SSISDB"].Folders["MasterChild"].Projects["MasterChildPackages"].Packages)
      {
      // Your code with myPackage here
      }

      Delete
  10. After deploying a web application on the web server (no SQL nor SSIS components installed) and running the application, there are errors "Could not load file or assembly Microsoft.SqlServer.IntergrationServices.[here goes a lot of different DLL names]". I've already added a few in the GAC folder but other appear to be missing. I thought that referencing DLLs from Step 3 is all I need. Am I missing something?

    ReplyDelete
    Replies
    1. This won't work without installing SSIS on the machine. Workarounds are 1) executing a stored procedure on the SQL Server machine that executes an SSIS package or 2) remotely executing a webservice which is running on the SQL Server machine that executes an SSIS package

      Delete

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