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


Invoking SSIS Package through a Web App


Invoking SSIS Package through a Web App

Author
Message
Hasitha Amarasena
Hasitha Amarasena
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 23
I have a requirement which needs to create an excel file based on a large customer table. The data will be retrieved from a data warehouse by running a stored procedure. In general, this is a very timely operation as it requires significant resources in and out. The web application uses Active Directory authorization which verifies the domain user credentials to give permission to execute the operation.

I have 2 options to achieve the above task. They are as follows;

1. In the ASP.NET side, invoke the SP > iterate through rows > create the excel file > open it on the browser.

2. Deploy a SSIS package that it projects the output to an excel > Synchronously invoke the SSIS package from the code > Wait until the operation completes > open the excel file on the browser.

I already created the package and it is invokable through the code. Also the code for the option 1 is in place. During the testing I learnt that the option 2 is more faster. However, I have few questions that would certainly assist me to take the most intelligent decision.

1. Is it good practice to invoke SSIS packages through a web application?

2. I know that the user who invokes the SSIS package need to have appropriate privileges in the Integration server. In the case of a web app, what I should exactly map when granting the permission? Is it going to be the domain user --> Integration Server or the web server --> Integration Server?

Thanks

Hasitha Amarasena
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14973 Visits: 14396
Hasitha Amarasena (1/24/2013)
I already created the package and it is invokable through the code. Also the code for the option 1 is in place. During the testing I learnt that the option 2 is more faster.

Invokable how? What are you calling that actually invokes the package? dtexec? A stored proc that uses xp_cmdshell to call dtexec?

On SQL Server 2005 the options were limited but here is a Microsoft article explaining the recommended methods for executing a package remotely:

Loading and Running a Remote Package Programmatically

1. Is it good practice to invoke SSIS packages through a web application?

In general, no. I would not do this work synchronously. Consider some type of queuing mechanism (see article above for two options) where the web app can submit a request to run the package and then immediately receive confirmation the request was accepted or rejected (this is actually how the proc used to start SQL Server Agent Jobs, msdb.sp_start_job, works). Then, some other process will run the SSIS package that outputs the file. From there the web app will need to wait for the asynchronous request to complete. The status of the asynchronous process (referring to the article this means either the Agent Job status or the Web Service will have to expose an execution status) can be polled by the web app, waiting for it to reach some final status like 'successful' or 'failed'. If successful then the web app would go looking for the output file where it should be, else it can report to the user the intermittent status as it polls or the final 'failed' status should the package fail.

In short, I would not recommend tightly coupling the web app to the execution of an SSIS package regardless of the method of invocation.

2. I know that the user who invokes the SSIS package need to have appropriate privileges in the Integration server. In the case of a web app, what I should exactly map when granting the permission? Is it going to be the domain user --> Integration Server or the web server --> Integration Server?

Read the article I provided. In the case of SQL Agent the web app will need to be able to start a specific job or submit a request to a web service. In the case of SQL Agent you can securely expose a way to start a single job by using a stored procedure signed with a certificate that is designed specifically to start one specific job, i.e. no input parameters and all it does is call msdb.dbo.sp_start_job to start one specific job. You could also have a proc that checks and returns the status of that same job, also signed with a certificate, which your web app would use when polling to see when the job is done.

edit: spelling

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
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