How to call Integration Services Project from Web UI

I just got done finishing an Integration Services Project (which I have to say was sickening easy!) which does the following:

1) Imports a comma delimited txt file

2) Exports it into a table

3) I do some manipulation and other table creation using SQL

4) Outputs a table to a flat file again

I now need to allow the user to run this process. I'd like to either:

a) Provide them a shortcut that when clicked on their desktop starts the process that I have defined in my Integration Services Project

b) Better yet, create a web U I that has a button they can click on, something that shows the progress in time, and then provides the output file as a downloadable link

I'd like to kno whow to do a & b just in case I decide to do one or the other at the end, I'd like to know how to do both for future reference ?

If the package is on the local machine (as well as SSIS), the easiest solution is to use the object model to load and execute the package. See "Running an Existing Package from a Client Application" in BOL. You can do this from any type of managed application, although you'll have extra permissions issues to iron out in a Web app.

If the package is not on the local machine (or the Web server), then you should configure an unscheduled SQL Agent job to run the package, and use ADO.NET from your app to launch the sp_startjob stored procedure on the server.

-Doug

|||

I>>>>f the package is not on the local machine (or the Web server), then you should configure an unscheduled SQL Agent job to run the package, and use ADO.NET from your app to launch the sp_startjob stored procedure on the server.

My Integration Services Project is calling the stored proc from within an "Execute SQL Task" module in my project. The whole point her is to take advantage of Integration services and it's workflow, after all why would I start a stored procedure outside of this when it's integrated in my packge?

So essentially, I want ASP.NET web button to fire of the start of my Integration Services package, just the same as I go into VS 2005 and click Play to run it. What command can call my package remotely to run it? How can I determine when it's done so I can show a processing bar on my web app?

I know this is possible, there has to be a way programically to invoke / call your Integration Services Project to run from a web page. That's the whole point in using Integration Services to do the dirty work with this stuff, I just want to be able to call it remotely from a web app in ASP.NET - a button or something that runs a script to run the project wherever it resides.

|||

I'm not certain how my response was misunderstood. My answer was precisely about the "way to programmatically invoke an Integration Services package from a Web page" or any other application.

If the package is local, I'd recommend using the API as described in the topic that I quoted, which takes about 2 lines of code (Load and Execute, as well as variable declarations).

You can also call dtexec.exe. If the package is not local, the normal way to launch it is through SQL Agent, by calling the sp_startjob stored procedure to launch the remote package, after configuring an unscheduled job that runs the package. In code, you would use ADO.NET to launch the stored procedure on the remote server.

-Doug

|||Ok, per your last response, now I understand. I have never done any of that before so I needed a more in depth response. Thanks.|||

No problem. Here's the VB code to launch a local package using the API, in case this wasn't in the RTM version of BOL...

Imports Microsoft.SqlServer.Dts.Runtime

Module Module1

Sub Main()

Dim pkgLocation As String
Dim pkg As New Package
Dim app As New Application
Dim pkgResults As DTSExecResult

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

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

End Sub

End Module

|||I really appreciate it, I didn't really know how to go about it. Thanks a lot!|||I'm also assuming I can run a package that is not local if I just tweak the filepath to use UNC or something?|||

Hi, I thought I had asked this question but don't see it in the topic thread. How do you call a SQL Agent job from a client app to run a SSIS package?

Thanks

|||

Using ADO.Net.

http://msdn2.microsoft.com/en-us/library/ms403355.aspx

-Doug

|||

You could also - as I would prefer - use SMO.

here are some hints:
http://www.fits-consulting.de/blog/PermaLink,guid,09a62245-9c7f-4d2a-aee2-7f30bbdee1c6.aspx

and here is the detailed link:
http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.agent.job.start.aspx

cheers,
Markus

|||Thanks, the VB example in the article is what I was looking for.