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

Question

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

vishal.shinde

Mr or Mrs. 500

Points: 538
More actions
February 15, 2010 at 2:14 am

#386313

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

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

hussain27syed Ten Centuries Points: 1191 More actions · Answer 1

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 SSC Guru Points: 246985 More actions · Answer 2

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

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Gift Peddie SSC Guru Points: 73570 More actions · Answer 3

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 SSC Guru Points: 259115 More actions · Answer 4

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.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

fellmeth Right there with Babe Points: 732 More actions · Answer 5

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 SSC Guru Points: 73570 More actions · Answer 6

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 SSCrazy Eights Points: 8694 More actions · Answer 7

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 Right there with Babe Points: 732 More actions · Answer 8

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 SSCrazy Eights Points: 8694 More actions · Answer 9

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.

Gift Peddie SSC Guru Points: 73570 More actions · Answer 10

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.

Now that I understand you the package in Asp.net does not run in the context of the Asp.net runtime or IUSR, it runs as a Job with a stored procedure in the context of the Agent account which must be admin but you run it as a proxy. In double hop you don't use constrained delegation because most networks run more than one version of the operating system. You instead add that Agent account in both servers and make sure the proxy works in as expected. so make sure the Agent is running with an admin account, create a proxy for that account, then create a Job and call the Job from a stored procedure in Asp.net the package must be running in the context of the Agent account.

And there is no passing of credential if you are in a Windows authentication application you can use Windows authentication but if you are in a Forms authentication application then you need to use SQL authentication so your application does not run into authentication related scalability issues.

Kind regards,
Gift Peddie

Rob CW SSC Enthusiast Points: 163 More actions · Answer 11

You could also consider writing an ASP.Net web service that uses the Microsoft.Sqlserver.Dts.Runtime assembly to execute SSIS packages. If you write the web service generically so that the name of the package and its variables are passed in as parameters then you'll have a very useul, lightweight mechanism to execute your SSIS packages with better low level control than what's on offer when using SQL Agent to execute them.

Deploy the web service to your SSIS box (be prepared to argue the case with any over-protective DBA that might object to running a web service on the SSIS server!), and you can then leverage all of the rich functionality of SSIS from any ASP.Net application by simply adding a web reference to your new web service.

mgbp SSC Enthusiast Points: 177 More actions · Answer 12

Hi,

Please help me with the scenarios below:

A-Server contains:

1. SSIS Package (dtsx)

2. textfiles to be imported in Database (of B-Server).

3. SSIS Component installed ONLY (no BIDS, no SQLServer-engine)

B-Server

1. SQL Server -engine (with target database).

2. WITHOUT SSIS Component installed.

--------------------------------------

I need to manually execute the .dtsx in A-Server using DTEXEC.exe in the command line.

This dtsx will import/convert the textfile to the database located in B-Server.

Both servers are in 64-bit.

Is it possible with the given situation above? What are the other requirements/components needed?

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 13

mgbp (4/21/2011)
Hi,
Please help me with the scenarios below:
A-Server contains:
1. SSIS Package (dtsx)
2. textfiles to be imported in Database (of B-Server).
3. SSIS Component installed ONLY (no BIDS, no SQLServer-engine)
B-Server
1. SQL Server -engine (with target database).
2. WITHOUT SSIS Component installed.
--------------------------------------
I need to manually execute the .dtsx in A-Server using DTEXEC.exe in the command line.
This dtsx will import/convert the textfile to the database located in B-Server.
Both servers are in 64-bit.
Is it possible with the given situation above? What are the other requirements/components needed?

You asked the exact same question over here:

http://www.sqlservercentral.com/Forums/Topic1055305-148-1.aspx#bm1096789

Instead of hijacking threads with a new question, just start a new thread please.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

COZYROC One Orange Chip Points: 28499 More actions · Answer 14

COZYROC has recently introduced the commercial SSIS NoW module. You can now implement SSIS package execution from web service address much easier and without requiring to implement a single line of code. Cheers!

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/