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