Execute SSIS from Asp.net Application

3
votes

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.

2
asp.netssis
I'd like to know the answer to this one as well. - NotMe
wtaniguchi and Eric, thanks very much for your help. Your solutions are probably better and more robust, but to adjust to our setup here, I did the following. This is how I got it to run: - LizardJack
My dba had to grant me rights to execute the sp_start_job task (which was done through a specific domain account). I believe he also had to give me rights to the following roles: SQLAgentOperatorRole SQLAgentReaderRole SQLAgentUserRole I then used the following microsoft code to run it via .net 2.0: msdn.microsoft.com/en-us/library/ms403355.aspx I took the code verbatim, and only had to change the following to have the name of my job: jobParameter.Value = "RunSSISPackage" - LizardJack

2 Answers

5
votes

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
1
votes

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.