August 1, 2006 at 1:43 pm
How can I run a SSIS package from an Access form with VB or from a stored procedure in sql server 2005?
August 2, 2006 at 8:52 am
The DTEXEC command will run a package, if Access has some version of a Shell function to run it. You can use xp_cmdshell (if it is enabled) to run a package with DTEXEC from a stored procedure.
If you want to run jobs from stored procedures but don't want to enable xp_cmdshell, you can do it with an alert. It's more complicated, but it can be useful for tasks that require elevated permissions that you don't want to grant wholesale.
1. Create an unsheduled Agent job to do whatever it is you want to accomplish. Running an SSIS package, for example.
2. Create an Alert that is triggered by a specific error number (an unused number, not any error that might be raised accidentally), and define the response to be running the Agent job.
3. Any user or application can then use RAISERROR to start the job.
If you're willing to grant SQLAgentOperatorRole permissions to users you could skip the alert and they could use sp_start_job to run the job. This lets them run any job however. The alert method lets anyone run the job, but only the specific jobs you have set up an alert for.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply