none
Microsoft.SqlServer.Management.IntegrationServices namespace

    Question

  • After several tries, I am unable to find the Microsoft.SqlServer.Management.IntegrationServices.dll or the namespace stated in the title.

    I am trying to write a C# application that will execute a SSIS 2012 package that I deployed to the Integration Services Catalog.

    I appreciate any assistance you can lend me in this matter.

    Tuesday, May 15, 2012 3:31 PM

Answers

All replies

  • After several tries, I am unable to find the Microsoft.SqlServer.Management.IntegrationServices.dll or the namespace stated in the title.

    Is the SSIS 2012 insatlled? And how did you search?

    Search in Windows Explorer for the needed DLL to reference.


    Arthur My Blog

    Tuesday, May 15, 2012 3:35 PM
    Moderator
  • Yes, I have installed the developer version of sql server 2012.  I am able to develop and deploy a package to the Integration Services catalog.

    I used windows search.  I also look in the C:\Program Files\Microsoft SQL Server\110\DTS\Binn and C:\Program Files(x86)\Microsoft SQL Server\110\DTS\Binn

    I looked in the Assemblies folder as well.

    Tuesday, May 15, 2012 4:06 PM
  • For what it is worth, I do not see that assembly on my SQL 2012 system either.

    Chuck

    Tuesday, May 15, 2012 4:08 PM
  • What do you need to accomplish?

    Arthur My Blog

    Tuesday, May 15, 2012 4:08 PM
    Moderator
  • For what it is worth, I do not see that assembly on my SQL 2012 system either.

    Chuck

    Microsoft.SQLServer.ManageedDTS.dll is the main API holder.

    Arthur My Blog

    Tuesday, May 15, 2012 4:16 PM
    Moderator
  • For what it is worth, I do not see that assembly on my SQL 2012 system either.


    Chuck

    Microsoft.SQLServer.ManageedDTS.dll is the main API holder.

    Arthur My Blog

    True, but all of the SSIS catalog documentation refers to Microsoft.SqlServer.Management.IntegrationServices.dll

    Chuck

    Tuesday, May 15, 2012 4:17 PM
  • As I stated before, I am trying to write a C# application that will execute an SSIS 2012 package that I have already deployed to the Integration Services Catalog.

    Thanks again for your help.

    Tuesday, May 15, 2012 4:34 PM
  • using System;
    using Microsoft.SqlServer.Dts.Runtime;
    
    namespace RunFromClientAppWithEventsCS
    {
      class MyEventListener : DefaultEvents
      {
        public override bool OnError(DtsObject source, int errorCode, string subComponent, 
          string description, string helpFile, int helpContext, string idofInterfaceWithError)
        {
          // Add application-specific diagnostics here.
          Console.WriteLine("Error in {0}/{1} : {2}", source, subComponent, description);
          return false;
        }
      }
      class Program
      {
        static void Main(string[] args)
        {
          string pkgLocation;
          Package pkg;
          Application app;
          DTSExecResult pkgResults;
    
          MyEventListener eventListener = new MyEventListener();
    
          pkgLocation =
            @"C:\Test\TestPKG.dtsx";
          app = new Application();
          pkg = app.LoadPackage(pkgLocation, eventListener);
          pkgResults = pkg.Execute(null, null, eventListener, null, null);
    
          Console.WriteLine(pkgResults.ToString());
          Console.ReadKey();
        }
      }
    }


    Arthur My Blog

    Tuesday, May 15, 2012 5:46 PM
    Moderator
  • Except that he deployed his packages to the 2012 integration services catalog (SSISDB) - there is no file path  @"C:\Test\TestPKG.dtsx";

    Chuck

    Tuesday, May 15, 2012 5:52 PM
  • Thank you, Chuck!  I could not have said it better myself.

    Tuesday, May 15, 2012 5:54 PM
  • Finally found what I was looking for:

    http://msdn.microsoft.com/en-us/library/hh479588.aspx

    An execution is an instance of a package execution. Call catalog.create_execution (SSISDB Database) and catalog.start_execution (SSISDB Database) to create and start an execution. To stop an execution or a package/project validation, call catalog.stop_operation (SSISDB Database).

    Basically the packages are kicked off by calling stored procedures in the SSISDB


    Chuck

    • Marked as answer by Bryan Shaw Wednesday, May 16, 2012 12:14 PM
    Tuesday, May 15, 2012 5:55 PM
  • And here is some sample code (the SQL part) that you would execute with your c#

    http://sqlblog.com/blogs/davide_mauri/archive/2011/11/24/execute-a-ssis-package-in-sync-or-async-mode-from-sql-server-2012.aspx

    As for the location of the mysterious integration services DLL, I'm still looking


    Chuck


    Tuesday, May 15, 2012 5:57 PM
  • Good point

    then

    LoadFromSqlServer(\\TestPKG, "(local)", "sa", "mypwd", Nothing);


    Arthur My Blog

    Tuesday, May 15, 2012 5:57 PM
    Moderator
  • Good point

    then

    LoadFromSqlServer(\\TestPKG, "(local)", "sa", "mypwd", Nothing);


    Arthur My Blog

    That syntax is for the old style MSDB storage, not Integration Services Catalog

    Chuck

    Tuesday, May 15, 2012 6:05 PM
  • Chuck is correct.
    Tuesday, May 15, 2012 6:34 PM
  • Why? See http://msdn.microsoft.com/en-us/library/ms403355.aspx please;

    In the aforementioned MSDN article at the bottom of which there is an example on how to do that with C# against the SSIS 2012 Catalog.

    The syntax is

    integrationServices.LoadFromSqlServer(packageName, "(local)", String.Empty, String.Empty, null);


    Arthur My Blog

    Tuesday, May 15, 2012 6:43 PM
    Moderator
  • Why? See http://msdn.microsoft.com/en-us/library/ms403355.aspx please;

    In the aforementioned MSDN article at the bottom of which there is an example on how to do that with C# against the SSIS 2012 Catalog.

    The syntax is

    integrationServices.LoadFromSqlServer(packageName, "(local)", String.Empty, String.Empty, null);


    Arthur My Blog

    If you read the article that you just linked to you will see that the object is only capable of running from file/msdb/ssis package store - not the integration services catalog. Have you tried this with a package that is in the catalog? If so then I'm curious to know how you specified the path into the catalog.

    Which section of that code do you think refers to the 2012 IS catalog??


    Chuck


    Tuesday, May 15, 2012 6:51 PM
  • Chuck,

    Oh, OK, I did not see the catalog needs to be used. But there is no MSDB option anymore in SSIS 2012. Hence the correct code (did not try myself) must be:

    integrationServices.LoadFromSqlServer(@"\SSISDB\MyCatalog\PackageName.dtsx", "(local)", String.Empty, String.Empty, null)


    Arthur My Blog

    Tuesday, May 15, 2012 7:14 PM
    Moderator
  • Bryan,

    please validate that

    integrationServices.LoadFromSqlServer(@"\SSISDB\Demo\MyCatalogProject\PackageName.dtsx", "(local)", String.Empty, String.Empty, null);

    code works.


    Arthur My Blog

    Tuesday, May 15, 2012 7:15 PM
    Moderator
  • Chuck,

    Oh, OK, I did not see the catalog needs to be used. But there is no MSDB option anymore in SSIS 2012. Hence the correct code (did not try myself) must be:

    integrationServices.LoadFromSqlServer(@"\SSISDB\MyCatalog\PackageName.dtsx", "(local)", String.Empty, String.Empty, null)


    Arthur My Blog


    There sure is an MSDB option in 2012 - it is still where they keep the data collector and maint plans.  Have you used SSIS 2012 yet?

    Chuck

    Tuesday, May 15, 2012 7:16 PM
  • Right, to support the backward comparability. There I saw a note on MSDN somewhere to not to use the LoadFromSQLServer directly from code, I understand the idea is to tell developers to stay away from this method of running packages. Yet, it seems PowerShell will be given a priority interacting with the catalog.

    There could be a way, I have the SSIS 2012 installed on three machines. Using it since RC0 on a VM. There is one client I am developing for already in SSIS 2012, but no deployment yet. None of the packages will be executed though using home-grown apps.

    In my past experience most SSIS packages were executed on demand externally either with sp_start_job in SQL or a BAT, but the most traditioonal approach is to use the SQL Agent.


    Arthur My Blog

    Tuesday, May 15, 2012 7:25 PM
    Moderator
  • I will have to delay looking at this until tomorrow morning.
    Tuesday, May 15, 2012 7:34 PM
  • I will have to delay looking at this until tomorrow morning.

    What you will find when you try is that the Microsoft.SqlServer.Dts.Runtime which comes with SQL2012

    namespace has changed all of the old references to .Wrapper.Package (etc)

    The LoadFromSQLServer method returns a IDTSPackage100

    So you'll need to declare your Package variable as IDTSPackage100 and that you will get an error message :

    Cannot find folder "\SSISDB\Test\Integration Services Project1\Package.dtsx"  When attempting to access a package in the catalog in the way described.


    Chuck

    Tuesday, May 15, 2012 8:08 PM
  • Thank you for validating this Chuck.

    What do we say then? There is no way you can use custom .Net code to interact with the SSIS 2012 catalog?


    Arthur My Blog

    Tuesday, May 15, 2012 8:13 PM
    Moderator
  • You can use the method I showed which involves calling the SSISDB procedures.   Until the mysterious dll reappears.  Odd that I find several posts from members of the SSIS team using that dll in powershell and c# examples - most of them from Nov 2011

    Chuck


    Tuesday, May 15, 2012 8:17 PM
  • But this is a T-SQL way, whereas Bryan wanted to use C#.

    Apparently the

    LoadFromSqlServer

    method is lacking the support for the SSIS 2012 catalog publicly.

    Yet, there is no .net equivalent alternative for interoperability with the SSIS 2012 Catalog.

    This looks like an omission to me. How about creating a MS Connect item for this?


    Arthur My Blog

    Tuesday, May 15, 2012 8:30 PM
    Moderator
  • But this is a T-SQL way, whereas Bryan wanted to use C#.

    You're kidding right?

    Open up a connection in C# and you execute the sql procs - that is a c# solution.

    As for the rest of it, who knows.  Maybe they expect the only interface to be via the stored procedures.  I was actually wondering before why they were exposing all of that DB structure, now maybe it makes sense.   It is a bit suprising how little documentation seems to be out there in terms of executing the new catalog packages.  

    One more note - it doesn't appear that the 2012 version of dtexecui is capable of executing a catalog package either.


    Chuck


    Tuesday, May 15, 2012 9:18 PM
  • Just like to add my voice to the missing DLL.  I installed the Developer version of 2012, but could not locate the assembly and naturally the namespaces therein.  I was attempting to follow a blog post about setting up the SSISDB Catalog, but was amazed that the referenced namespace did not exist anywhere on my machine.  I even tried to do a repair and search for a related check box in the installation wizard for the assembly, but no luck. =(
    Tuesday, May 15, 2012 9:33 PM
  • FWIW - here is some sample code I tossed together to test out the package execution from the catalog. 

    using System;
    using System.Web;
    using System.Data;
    using System.Data.SqlClient;
    using System.IO;
    
    
    namespace SSISDBPAckageTest
    {
        class Program
        {
            static string _connectionString = "Persist Security Info=False;Integrated Security=true;Initial Catalog=SSISDB;server=(local)";
    
            static void Main(string[] args)
            {
                Int64 executionID;
    
                executionID = CreateExecution("Package.dtsx", "Test", "Integration Services Project2", false, null);
                SetExecutionParameterValue(executionID, 20, "ProjectTestParam1",661);
                SetExecutionParameterValue(executionID, 30, "PackageTestParam1", 662);
    
                StartExecution(executionID);
                        
                        
            }
            static Int64 CreateExecution(string packageName, string folderName, string projectName, bool use32BitRuntime, Int64? referenceID)
            {
                SqlConnection conn = new SqlConnection(_connectionString);
                conn.Open();
                //Create the execution
                //EXEC [SSISDB].[catalog].[create_execution] @package_name='my_package.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'BI', @project_name=N'DWH', @use32bitruntime=False, @reference_id=Null 
                SqlCommand cmd = new SqlCommand("catalog.create_execution", conn);
                cmd.CommandType = CommandType.StoredProcedure;
    
                cmd.Parameters.Add(new SqlParameter("@package_name", packageName));
    
                cmd.Parameters.Add(new SqlParameter("@folder_name", folderName));
                cmd.Parameters.Add(new SqlParameter("@project_name", projectName));
                cmd.Parameters.Add(new SqlParameter("@use32bitruntime", use32BitRuntime));
                cmd.Parameters.Add(new SqlParameter("@reference_id", referenceID));
                SqlParameter outParm = cmd.Parameters.Add(new SqlParameter("@execution_id", 0));
                outParm.DbType = DbType.Int64;
                outParm.Direction = ParameterDirection.Output;
    
                cmd.ExecuteNonQuery();
                conn.Close();
                return (Int64)cmd.Parameters["@execution_id"].Value;
            }
    
            static void StartExecution(Int64 executionID)
            {
                SqlConnection conn = new SqlConnection(_connectionString);
                conn.Open();
                //Start the execution
                //EXEC [SSISDB].[catalog].[start_execution] @execution_id 
                SqlCommand cmd = new SqlCommand("catalog.start_execution", conn);
                cmd.CommandType = CommandType.StoredProcedure;
    
                cmd.Parameters.Add(new SqlParameter("@execution_id", executionID));
                cmd.ExecuteNonQuery();
                conn.Close();
                return;
            }
    
            static void SetExecutionParameterValue(Int64 executionID, int objectType, string parameterName, object parameterValue)
            {
                SqlConnection conn = new SqlConnection(_connectionString);
                conn.Open();
                //The type of parameter. Use the value 20 to indicate a project parameter or the value 30 to indicate a package parameter. The object_type is smallint.
                //EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0 
                SqlCommand cmd = new SqlCommand("catalog.set_execution_parameter_value", conn);
                cmd.CommandType = CommandType.StoredProcedure;
    
                cmd.Parameters.Add(new SqlParameter("@execution_id", executionID));
                cmd.Parameters.Add(new SqlParameter("@object_type", objectType));
                cmd.Parameters.Add(new SqlParameter("@parameter_name", parameterName));
                cmd.Parameters.Add(new SqlParameter("@parameter_value", parameterValue));
    
                cmd.ExecuteNonQuery();
                conn.Close();
                return;
    
            }
        }
    }


    Chuck

    Wednesday, May 16, 2012 2:53 PM
  • And I was thinking there must be a way of executing packages w/o using the SQL Command.

    Appears this is a tad more trickier, fortunately there is only one difficulty to overcome.

    1st we need to use .Net Framework 3.5 as the target,

    2nd, since the reference to Microsoft.SqlServer.Management.IntegrationServices would not come up - you manually add it right into the Visual Studio project file. The text you need to add is:

    <Reference Include="Microsoft.SqlServer.Management.IntegrationServices,
    Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91,
    processorArchitecture=MSIL" />

    It then magically shows up in the References folder and thereafter you can use it consume it in code (after you have added the using Microsoft.SqlServer.Management.IntegrationServices; directive).

    Then you can craft the code very well similar to what is in this blog post:

    http://blogs.msdn.com/b/mattm/archive/2011/11/17/ssis-and-powershell-in-sql-server-2012.aspx

    But instead of using the PowerShell adopt it to C# code

    e.g. Server myServer = new Server(myConn);


    Arthur My Blog


    Wednesday, May 16, 2012 3:39 PM
    Moderator
  • Where do you find that assembly on your machine?  And if you found it on your machine what version of SQL 2012 are you using?

    Chuck

    Wednesday, May 16, 2012 3:41 PM
  • Interesting - it is actually in the GAC on my machine. 

    Chuck

    Wednesday, May 16, 2012 3:46 PM
  • It does work.  Little sample code here (I used .Net 4.0 with no issues)

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data.SqlClient;
    using Microsoft.SqlServer.Management.IntegrationServices;
    using Microsoft.SqlServer.Management.Sdk.Sfc;
    using Microsoft.SqlServer;
    
    
    
    namespace ConsoleApplication2
    {
        class Program
        {
            static void Main(string[] args)
            {
    
                SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=SSISDB;Integrated Security=SSPI;");
    
                IntegrationServices isserver = new IntegrationServices(conn);
    
                ProjectInfo testProject = isserver.Catalogs["SSISDB"].Folders["TEST"].Projects["Integration Services Project2"];
                PackageInfo testPackage = testProject.Packages["Package.dtsx"];
    
    
                testProject.Parameters["ProjectTestParam1"].Set(ParameterInfo.ParameterValueType.Referenced, "661");
                testPackage.Parameters["PackageTestParam1"].Set(ParameterInfo.ParameterValueType.Referenced, "662");
    
                
                testPackage.Execute(false, null);
    
            }
        }
    }

    with the following refs

    <Reference Include="Microsoft.SqlServer.ConnectionInfo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL" />
        <Reference Include="Microsoft.SqlServer.Management.Sdk.Sfc, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL" />
        <Reference Include="Microsoft.SqlServer.Smo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL" />
        <Reference Include="System" />
        <Reference Include="System.Core" />
        <Reference Include="System.Xml.Linq" />
        <Reference Include="System.Data.DataSetExtensions" />
        <Reference Include="System.Data" />
        <Reference Include="System.Xml" />
        <Reference Include="Microsoft.SqlServer.Management.IntegrationServices, &#xD;&#xA;Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, &#xD;&#xA;processorArchitecture=MSIL" />


    Chuck



    Wednesday, May 16, 2012 4:05 PM
  • Another $0.02: turns out if targeting SQL Server 2012 the target must be the .Net 4.0, not 3.5. The
    IntegrationServices
    DLL is compiled against .Net 4

    Arthur My Blog

    Wednesday, May 16, 2012 4:11 PM
    Moderator
  • How do you check the status or execution result of the package?

    Wednesday, May 16, 2012 7:46 PM
  • Good question - the execute method returns an Int64 which is the execution_id

    http://msdn.microsoft.com/en-us/library/ff929982.aspx

    There is probabally a way to get at the execution log using the execution_id.  There is a ExecutionOperationCollection object but not really any sample code on how to use it.

    In SSMS you can look at the Standard Reports on the Folder and use the Execution report to show you the executions.

    There is a blurb in a sample chapter from this book discussing the collection.  Guess I'll have to add this one to my purchase list

    http://www.amazon.com/Professional-Microsoft-Server-Integration-Services/dp/111810112X

    Looks like it is Catalog.Executions which will return the list to you


    Chuck




    Wednesday, May 16, 2012 7:57 PM
  • Here it is, well hidden in the deep mines of moria:

    C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.IntegrationServices\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.IntegrationServices.dll

    Wednesday, April 09, 2014 1:53 PM
  • Is the assembly redistributable?
    Thursday, January 08, 2015 3:42 PM
  • The package is part of the .NET framework, from 2.0 onwards. So you will find in on the GAC (C:\windows\assembly).


    Rodrigo Medeiros. Twitter: @rationaldba

    Thursday, January 29, 2015 12:03 PM
  • Hi Chuck,

    Is there anyway to download the SSIS package from 2012 catalog using .Net or converting to XML and save to our local. Appreciate if you could help me on this.

    Kiran.K

    Thursday, July 02, 2015 2:56 PM
  • Hi there

    This post worked for me using C# only (not just calling stored procedures from C#)...

    http://consulting.risualblogs.com/blog/2014/07/07/executing-ssis-catalog-packages-programmatically-with-c/comment-page-1/

    Hope that helps...

    As for this missing DLL's everyone seems to be talking about, you've got to make sure have SQL Server Data Tools for BI (otherwise known as SSDT BI) installed on your client development machine. Note that this means SQL Server Data Tools for BI not just the standard SSDT (yes, there are 2 types of SSDT!).

    Wednesday, September 16, 2015 9:06 AM