Execute Integration Package from a C# Solution

Hi all
Does somebody know how I can execute a SSIS Package on a SQL 2005 Server?
I need to execute from a C# Solution which is running on a client.
Thanks for any comments
Best regards
Frank Uray

On the server, configure an unscheduled SQL Agent job. On the client, use ADO.NET to invoke the sp_startjob system stored procedure.

It would also be possible to create a simple CLR stored procedure that uses the Integration Services API to launch a package on the server. I am working on the sample code for this today.

-Doug

|||

Hi Doug

You mean that there is no Namespace for accessing SSIS Packages ?
On SQL Server 2000, there was this need dts Namespace (API) ...

Thanks anyway

Best regards
Frank Uray

|||

Yes, certainly there is a very complete Integration Services API, and you'll note that I mentioned it in my reply.

However I'm not certain, without testing, whether you can use the API from a client to execute packages. As you know, a package runs on the computer it is called from (therefore, in this case, the client), and NOT on the computer where it is stored.

Integration Services is now a server platform and no longer a redistributable tool like DTS. Although installing Tools on the client gives you enough of SSIS to design packages, they cannot normally be executed outside the IDE.

Running the package on the server where it is stored -- as the SQL Agent solution accomplishes -- actually avoids a lot of common issues with paths to resources that are not available from client computers.

-Doug

|||

Hi Doug

Thanks again for your answer !

Yes, I have seen it on your first replay but I don't want to execute through
a stored procedure ...
The idea is to execute the Package on the server,
as if you execute a stored procedure. They also don't run on the client.
For example: I make a SQL connection and than start the package async. on
this created connection ...

Do you have a sample code for executing a SSIS Package in a
stored procedure on the 2005 server?

Best regards
Frank Uray

|||

Using the API will launch the package wherever the API code runs...that's why you would want to put the code in a CLR stored procedure to ensure that it runs on the server.

I won't have my sample finished and tested until later today, but it's basically the same "run a package" code shown in "Running an Existing Package from a Client Application ," wrapped in a CLR procedure with input params for package name etc as needed.

using Microsoft.SqlServer.Dts.Runtime;

namespace RunFromClientAppCS
{
class Program
{
static void Main(string[] args)
{
string pkgLocation;
Package pkg;
Application app;
DTSExecResult pkgResults;

pkgLocation =
@."C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Package Samples\CalculatedColumns Sample\CalculatedColumns\CalculatedColumns.dtsx";
app = new Application();
pkg = app.LoadPackage(pkgLocation, null);
pkgResults = pkg.Execute();

Console.WriteLine(pkgResults.ToString());
Console.ReadKey();

}
}
}

|||

Hi Doug

Thanks a lot, that is helping !

Would be happy to get your sample when you have it finished.

Best regards
Frank Uray

|||

Frank,

It looks like my idea of a CLR stored procedure to launch packages using the Integration Services API may not be feasible. I've run into too many errors and security complications trying to register the Runtime Wrapper interop assembly and the Runtime assembly in SQL Server before I can even start on the simple code.

It sounded like a simple and useful concept, but SQL CLR is extremely fussy about what's going to run inside the SQL process space (understandably).

-Doug

|||Doug,

I ran into the exact same issue you did. I needed to invoke a package from an asp.net app and wanted to do it via a CLR stored proc. Oh well...

I documented all my SSIS experiences in a series of articles:

http://www.blognet.info/weblogs/entry.php?u=adhalejr&e_id=849

Maybe that will provide some pointers.

Donnie