views:

39

answers:

1

i'm using Sql Server 2008. There are options to run from command line. Is there a way to execute an ssis package using sql statement?

-Vivek

+2  A: 

Im my experience one of the easiest way to control them from within SQL is not to try an xp_cmdshell etc but to add a job that executes the SSIS package as the first step and then from within the SQL msdb.dbo.sp_start_job 'yourjobname'

This also makes it easy to control which account (via proxy / credential) the job gets run under.

Andrew
any other direct way of calling an ssis package from sql statement?
Vivek Chandraprakash
Only other option that I know of it via xp_cmdshell but that is just a horrid option in comparison and requires xp_cmdshell access which itself is a security problem. Via the job you can track via the job history table when it completes, via cmd shell you will not.
Andrew
Thanks a lot. I will take this approach.
Vivek Chandraprakash
I have a webpage that the customer uses to export some records from the database to the mdb file. I created ssis for this purpose. In my previous method i used dts package and ran it using xp_cmdshell, so that it waits until teh export is done. now in this run a job approach, the handle is given to the job scheduler. How do i make the webpage wait until the job is completed so that i can give the link to the client to download the mdb file.-Vivek
Vivek Chandraprakash
The job history can be pulled via sp_help_jobhistory and can give you a run_status http://msdn.microsoft.com/en-us/library/ms188025.aspx
Andrew