Call SSIS package residing on DB Server from a web application ASP.NET residing on Web server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » Call SSIS package residing on DB Server from...

14 posts, Page 1 of 2

1 2 »»

Call SSIS package residing on DB Server from a web application ASP.NET residing on Web server

Rate Topic

Display Mode

Topic Options

Author

Message

vishal.shinde

Posted Monday, February 15, 2010 2:14 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, March 09, 2012 4:17 AM
Points: 12, Visits: 113

I have a requirement where I need to call a SSIS package from a ASP.NET application.
The SSIS package is residing on DB Server (created using BI studio) and client application is on another web server which do not have SSIS or SQL Server installed.

How can I achieve this and what are the prerequisits for achieiving this. Any help is appreciated. Or is there any other way to achieve this requirement?

Thanks
Vishal

Post #865414

hussain27syed

Posted Monday, February 15, 2010 7:35 AM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, August 13, 2011 3:28 AM
Points: 41, Visits: 112

have you ever tried to start the job on the server with sp_start_job. you can connect to the database server and run this procedure
i guess this store procedure should work though i have never tried it myself (' BigGrin

');

do try and let me know if it works

Post #865533

Phil Parkin

Posted Monday, February 15, 2010 7:50 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:19 AM
Points: 4,234, Visits: 9,469

You obviously need to create a job to run the package before trying that idea ... it should work.

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #865539

Gift Peddie

Posted Monday, February 15, 2010 9:31 AM

Hall of Fame

Group: General Forum Members
Last Login: Friday, March 29, 2013 11:17 AM
Points: 3,432, Visits: 14,332

vishal.shinde (2/15/2010)

Yes you can run it as a job with a stored procedure but this is double hop so the Asp.net runtime needs permissions in both boxes or you will get null user error.

You also should run the agent with admin level permission because you can run it as a proxy.

Kind regards,
Gift Peddie

Post #865589

Koen Verbeeck

Posted Tuesday, February 16, 2010 1:00 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 9:06 AM
Points: 9,367, Visits: 6,465

You could also send the execution command directly to the server. Something in the like of:

"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /SQL "\Folder\MyPackage" /SERVER "MyServer" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E

Note that this command is for a 64-bit machine. You should check where the dtexec.exe file is located on your server. The package MyPackage is deployed to the MSDB folder on the Integration Services server, located on the SQL Server.

I have however no idea what the security settings should be to make this work from ASP.NET.

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #865882

fellmeth

Posted Tuesday, February 16, 2010 8:30 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 6:59 AM
Points: 8, Visits: 68

I can share with you that we recently tried to make this happen via our Intranet, and you are going to run into authentication/permission issues. We got the package stored in MSDB to execute via ASP.NET page when running page from Visual Studio server on a remote admin machine, but when the page was deployed to the Intranet, the package would fail. Looked all over for a way to make it work, but there's no obvious workaround that we could find. We then created a job for the package and called an SP from ASP.NET page to run the job, but we had similar issues with that, and we finally gave up. Lots of people seem to have the idea that in theory this should work, but I have not found anyone who has been able to successfully achieve it from IUSR or ASPNET user accounts.

Post #866150

Gift Peddie

Posted Tuesday, February 16, 2010 10:01 AM

Hall of Fame

Group: General Forum Members
Last Login: Friday, March 29, 2013 11:17 AM
Points: 3,432, Visits: 14,332

We got the package stored in MSDB to execute via ASP.NET page when running page from Visual Studio server on a remote admin machine, but when the page was deployed to the Intranet, the package would fail.

I don't understand what you mean by deploy to intranet because the Agent it is automation tool so it needs to be in MSDB and not file system because if you deploy it to file system then you need to take care of Asp.net file permission and could escalate to Asp.net reflection permissions. That is the reason you use the Agent subsystem for all Asp.net execution.

Kind regards,
Gift Peddie

Post #866203

Ed Zann

Posted Wednesday, February 17, 2010 8:00 AM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 6:45 AM
Points: 470, Visits: 1,051

We execute dozens of SSIS packages daily from a custom .Net "Operator Console" desktop app we developed. It's not ASP.Net but the concepts would be the same. Basically the app calls sp_start job which runs the SSIS package. It greatly simplifies the execution of the packages for the operators - just point and click. They can monitor the status of jobs and the success/failed outcome.

There are of course SQL security considerations. The account calling sp_start_job must have execute permissions. In our case the jobs run in the context of the SQL Agent which has the appropriate permissions to the resources needed.

Post #867102

fellmeth

Posted Wednesday, February 17, 2010 8:27 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 6:59 AM
Points: 8, Visits: 68

Sorry, I don't understand your post. Let me clarify on my post. The ASP.NET page was developed locally by an Admin Windows and Admin SQL user. When he executed the page locally thru VS server, the package, stored in MSDB, executed successfully. When the ASP.NET page was deployed to our Intranet, the package execution would fail. We believe the reason has to do with the credentials not being passed properly. We tried a number of different tricks, including Forms Authentication, ASP.NET Impersonation, and finally running the app in a separate application pool with admin SQL user. Nothing worked. I'm not suggesting that it can't be done from ASP.NET but rather that we were not able to resolve. There does not appear to be an easy answer to the authentication issues as passing credentials from ASP.NET to SQL to SSIS Package execution or to Job execution is nonobvious and pretty ornery.

Post #867140

Ed Zann

Posted Wednesday, February 17, 2010 8:49 AM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 6:45 AM
Points: 470, Visits: 1,051

Well, it sounds like you're experiencing the "double-hop" issue. If you do not have Kerberos authentication enabled between these servers on your network (I do not know much about this) you cannot pass the credentials of the web client user to the web server (first hop) and then to the SQL Server (second hop).

One solution would be to create a standard SQL login with the appropriate permissions to execute sp_start_job. Use this login in your asp.net connection string to call the stored procedure that executes the SSIS package.

It is not ideal, but it works. However, the job is always called by the sql login that was created, so you do lose the audit trail back to the actual user.

Post #867169

« Prev Topic | Next Topic »

14 posts, Page 1 of 2

1 2 »»

Permissions