Execute SSIS from Asp.net Application

Question

Hello,

I'm trying to run a simple SSIS package (copies data from an outside folder into a SQL 2005 table). I want to run this package from an Asp.net 2.0 application. Any suggestions?

I have searched many different blogs and websites, but all of these methods lead to failure (usually because of security issues)

dtexec /FILE "name of package" etc.

EXEC master..xp_cmdshell @cmd (supposedly a very bad idea)

sp_start_job

app.LoadPackage(@"\servername\sharename\Package1.dtsx", null)

Thanks in advance for any help you can give me.

Answer 1

Well, there, Jack, what you've listed is a hodge-podge of half-right answers.

There are a lot of ways to start an SSIS package. However, the best way is probably sp_start_job. Now, this implies that you've created a job that will run your SSIS package.

A lot of times, this puppy fails because of credentials issues. Namely, you're trying to run the bloody thing as the SQL Server Agent account (aka LOCAL SYSTEM), which doesn't bode too well for execution abilities. So, you'll need to create a Credential, and then a Proxy for the job to run as. Therefore, what you need to do is thusly:

CREATE CREDENTIAL MyCred WITH IDENTITY 'CORP\MyUser', SECRET = '<PassGoesHere>'
GO
sp_add_proxy @proxy_name='MyProxy', @enabled = 1, @credential = 'MyCred'
GO
sp_grant_proxy_to_subsystem @proxy_name = 'MyProxy', @subsystem_id = 3
GO
sp_add_job @job_name = 'MyJob', @enabled = 1
GO
sp_add_jobstep 
    @job_name = 'MyJob', 
    @step_name = 'Run SSIS Package', 
    @subsystem = 'CMDEXEC', 
    @command = 'dtexec /F C:\Path\To\Package.dtsx', 
    @proxy_name = 'MyProxy'

Meaningful docs:

1. CREATE CREDENTIAL
2. sp_add_proxy
3. sp_grant_proxy_to_subsystem
4. sp_add_job
5. sp_add_jobstep

Answer 2

Using an ASP.NET application, server-side wise, is pretty much similar to any other .NET application I guess. I have a code sample here running a SSIS package (SQLServer 2008), but I cannot copy'n'paste it here since I'm on a NDA in this project.

But I can tell you, we are using a namespace called Microsoft.SqlServer.Dts.Runtime.Wrapper, interfaces Package and Application and DTSExecResult enum... and it works fine.

And we are importing an Excel sheet to our database as well.

Edit: Here's the overview of how we do it here: We have Excel sheets that contains data we want to import into our system. Our system is ASP.NET 3.5/Castle Project/SQLServer 2008 powered. Once the user uploads a new sheet into your system, we store it in a folder, let's say "c:\UPLOAD". An action is triggered, so we call our SSIS package to import that into SQL Server 2008.

How we implement it (C#):

Package package;
Application app;
DTSExecuteResult packageResult;

String packagePath = ""; // You have to get your physical path to your package.

app = new Application();
app.PackagePassword = "password"; // We have it here..
package = (Package)app.LoadPackage(packagePath, true, null);
packageResult = package.Execute();

All those classes are from the same namespace, Microsoft.SqlServer.Dts.Runtime.Wrapper, as I stated before.

If you are having access problems, I guess you better ask your DBA to raise your permissions or even create a new one just to run SSIS's packages, since it requires additional levels of permissions.

Hope this can help you.