how to run SSIS in ASP.NET

Question

I Have the c# code here.

using Microsoft.SqlServer.Dts.Runtime; 

string filedts = "C:\\SSIS\\SQLtoFLAT\\PQFormTEST2.dtsx";    
Application app = new Application();
Package package = app.LoadPackage(filedts, null);
DTSExecResult result = package.Execute();

I see all website code is the same like this, but when I run it I in ASP.NET, There have no any error, but result got 'failure'.

why? How to fix it?

Thanks for any Help, Julia

Answer 1

The first thing I would suspect is that it is a security issue -- does the ASP.Net process have access to the .dtsx file?

Here's an easy way to check: download ProcessExplorer: http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx and run it while you attempt to execute this code

Process Explorer will monitor every file access, and it will tell you if it fails or succeeds. Chances are, you will see a failure opening your dtsx file.

(of course you could just grant permissions to the dtsx file off the bat, but it's worth taking the time to learn process monitor -- excellent tool for diagnosing this sort of thing).

Answer 2

I'd rather either deploy the dtsx on the SQL Server and create a Job that will be run asynchronously from the WebSite (using SMO).

Creating a Job is easy enough and I suppose You have a SQL Server if You have sSIS packages. You just create a step that is RUN SSIS Package. You then choose one of the packages deployed on the server and make any additional steps f You want.

Running the Jobs from The ASP.NEt requires Server Management Objects API. Generally You obtain a reference to the server. Enumerate for a Job to run. and then Click run.

Of course If Your package use xml configuration You will need to create a file (from ASP.NET or other layer). hope it helps. I will try to post a code sample later.

Answer 3

The most common problem I see with running SSIS packages from .Net apps is a misunderstanding of where they're run. SSIS packages are not server-based execution units that are run in the context of the server they reside on, like T-SQL commands or stored procedures. The SSIS package is loaded into the process you're calling it from, and executed locally. This means that:

• SSIS must be installed on your web server, or
• You need to architect a remote execution solution for SSIS (dtExecRemote, SQL Agent Jobs, ...)

Answer 4

You can create a job that runs the package, create a stored procedure that runs that job, then call the SP as you would usually. It's pretty simple...

ALTER PROCEDURE [dbo].[ExecJob]
-- Add the parameters for the stored procedure here
@spName varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
DECLARE @r INT 

--BEGIN TRY 
    EXEC @r = msdb.dbo.sp_start_job @spName
--END TRY 
--BEGIN CATCH 

--END CATCH

SELECT @r
END

note that the @r returned is the job status code