SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
vishal.shinde
vishal.shinde
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 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
hussain27syed
hussain27syed
SSC Veteran
SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)

Group: General Forum Members
Points: 215 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 (':-D');

do try and let me know if it works
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50471 Visits: 21152
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Gift Peddie
Gift Peddie
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16824 Visits: 14456
vishal.shinde (2/15/2010)
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


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
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61145 Visits: 13297
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
fellmeth
fellmeth
SSC-Enthusiastic
SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)

Group: General Forum Members
Points: 148 Visits: 70
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.
Gift Peddie
Gift Peddie
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16824 Visits: 14456
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
Ed Zann
Ed Zann
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1950 Visits: 1398
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.



fellmeth
fellmeth
SSC-Enthusiastic
SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)

Group: General Forum Members
Points: 148 Visits: 70
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.
Ed Zann
Ed Zann
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1950 Visits: 1398
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.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search