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.

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

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.

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

What do you need to accomplish?Arthur My Blog

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

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

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.

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

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

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

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 SSISDBChuck

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

Good point then LoadFromSqlServer(\\TestPKG, "(local)", "sa", "mypwd", Nothing); Arthur My Blog

Good point then LoadFromSqlServer(\\TestPKG, "(local)", "sa", "mypwd", Nothing); Arthur My Blog That syntax is for the old style MSDB storage, not Integration Services CatalogChuck

Chuck is correct.

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

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

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

Bryan, please validate that integrationServices.LoadFromSqlServer(@"\SSISDB\Demo\MyCatalogProject\PackageName.dtsx", "(local)", String.Empty, String.Empty, null); code works.Arthur My Blog

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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 4Arthur My Blog

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

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