Following is the script (same as used in executing the package through Dtexec command line utility except the value of the variable Mode) that is used in creating a batch file (crate a text file, paste the script and rename it to .bat):
A batch file can be schedules using a task scheduler or SQL Agent. Create a job and this time the step would be OS type job step. Give the actual path of batch file as shown below:
This batch file can be scheduled using windows task scheduler.
Using sp_start_job stored procedure to execute a sql agent job
Stored procedure sp_start_job can be used to execute the already created job. This stored procedure accepts various parameters, job name or job id being the mandatory parameter. Value of the variable “Mode” cannot be changed using this procedure. To verify the result in table, edit the job and set the value of variable Mode as Procedure. Then execute the procedure as:
EXEC msdb.dbo.sp_start_job N'SSISMode'
The procedure is available in msdb database so use this database while executing the procedure. The name of the job is SSISMode. To verify the result, check the table for an entry with value for column Execution Mode as Procedure
Using xp_cmdshell to execute a package from SSMS
Xp_cmdshell is off by default and needs to be enabled by a sysadmin. Check following links to enable xp_cmdshell in SQL Server 2005 and 2008:
SQL Server 2005 and SQL Server 2008 Once the xp_cmdshell is enabled, write the command to execute the package. The command would be like: EXEC XP_CMDSHELL 'DTEXEC <Place Holder>' Use DtexecUI to set the value of variable Mode as Xp_cmdshell. Then click on Command Line tab and copy the command and paste it in the place holder above (