Saturday, January 16, 2010

Executing an SSIS package using batch file or stored procedure or xp_cmdshell

Creating a batch file and schedule it trough Task Scheduler or SQL Agent
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 (). After executing the package check and verify the result in the table by selecting a column with Execution Mode as Xp_cmdshell. Using this method to execute a package is not recommended because the use of xp_cmdshell requires sysadmin privilege and thus it opens up a security hole. Note: When pasting the command in management studio, don’t try to split the lengthy command into multiple lines (using enter key) to avoid scrolling left and right. Check this link to know why not to do so


1 comment:

  1. Hi All,

    Please suggest...

    I have a SSIS Package in SQL Server 2008 R2, I am invoking that SSIS Package from Stored Procedure (more than one time depending upon the requirement), SSIS Package will transform Data and load into 5-6 tables. The Package have only Control Flow tasks no Data Flow tasks, Data inserted is using Stored Procedures. I am calling this Package from Stored Procedure and Stored Procedure from Application. My Client is SQL Server Express. I am using Linked Server to connect to Server Database and invoking SSIS Package. But when i am using network connection (LAN) its taking 2 mins to execute the Package, but when i am on VPN its taking around 9-10 mins. Even i have Stopped Validation by making Delay Validation to True. I want this Package to be run on server only it can be invoke from client. I think its validating from SQL Express and taking long time using Linked Server. I want SSIS to be run on Server independently with Parameters, it should not validate anything from client, why becasue Client is far & using Linked Server.