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.

Answer 1

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

Answer 2

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.

Answer 3

For what it is worth, I do not see that assembly on my SQL 2012 system either.

---

Chuck

Answer 4

What do you need to accomplish?

---

Arthur My Blog

Answer 5

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

Answer 6

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

Answer 7

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.

Answer 8

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

Answer 9

Except that he deployed his packages to the 2012 integration services catalog (SSISDB) - there is no file path  @"C:\Test\TestPKG.dtsx";

---

Chuck

Answer 10

Thank you, Chuck!  I could not have said it better myself.

Answer 11

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

Answer 12

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

Answer 13

Good point

then

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

---

Arthur My Blog

Answer 14

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

Answer 15

Chuck is correct.

Answer 16

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

Answer 17

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

Answer 18

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

Answer 19

Bryan,

please validate that

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

code works.

---

Arthur My Blog

Answer 20

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

Answer 21

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

Answer 22

I will have to delay looking at this until tomorrow morning.

Answer 23

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

Answer 24

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

Answer 25

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

Answer 26

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

Answer 27

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

Answer 28

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

Answer 29

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

Answer 30

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

Answer 31

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

Answer 32

Interesting - it is actually in the GAC on my machine. 

---

Chuck

Answer 33

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

Answer 34

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

Answer 35

How do you check the status or execution result of the package?

Answer 36

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

Answer 37

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

Answer 38

Is the assembly redistributable?

Answer 39

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

Answer 40

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

Answer 41

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

Answer 42

Thank you for answering the Q of where the DLL came from.  I'm in a position where the C# code to interact with the SSIS Catalog runs just fine on developer PCs, but not on the target host machine, due to this DLL being absent.  We tried exporting from GAC on Dev PC and putting it into the project directly but it wasn't happy.  I'll go try installing SSDT / SSDT BI to see if that helps the target machine.

Answer 43

you should be able to find the file here: C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.IntegrationServices\

as explained in the Add References section here: https://docs.microsoft.com/en-us/sql/integration-services/ssis-quickstart-run-dotnet?view=sql-server-ver15