Invoking SSIS Package through a Web App

  • 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

  • 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

Viewing 2 posts - 1 through 1 (of 1 total)

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