Executing SSIS Packages from command prompt / SSMS

demo

“Dtexec” utility can be used to run the SSIS packages from the command prompt. Using dtexec utility you can load packages from a Microsoft SQL Server database, the SSIS service, and the file system.

The general syntax is

1. dtexec /option [value] [/option [value]]…

复制代码

When a package runs, dtexec can return an exit code. Given below are the various exit values.
0 = execution successful
1 = package failed
3 = execution canceled
4 = the package could not be located
5 = unsuccessful in loading the package for execution
6 = error in the supplied command line


An example of “dtexec” is as given below

1. dtexec /f D:\Testing_Packs\testing_team\package3.dtsx
   
2. /SET \package.Variables[User::MyVariable].Properties[Value];VarValue1
   
3. /SET \package\DataFlowTask1.Variables[User::newVariable].Properties[Value]; VarValue2

复制代码

You can execute the packages from SSMS and stored procs/functions using “XP_CMDSHELL” .Given below is an example

1. Exec xp_cmdshell ‘dtexec /f D:\Testing_Packs\testing_team\package3.dtsx
   
2. /SET \package.Variables[User::MyVariable].Properties[Value]; VarValue1
   
3. /SET \package\DataFlowTask1.Variables[User::newVariable].Properties[Value]; VarValue2 ‘

复制代码

You might get an error while executing the above script , which might look like

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1

SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure.

This is because, as a security safeguard, Microsoft has turned off the use of xp_cmdshell by default . This has to be enabled by the users.
Follow the steps given to enable XP_CMDSHELL
1. Click the Start button.
2. Navigate to the Microsoft SQL Server 2005 folder.
3. On the flyout, mouseover Configuration Tools.
4. Select SQL Server Surface Area Configuration.
5. At the bottom of the window, select Surface Area Configuration for Features. You will get a window as in the image below.
6. In the left pane, under Database Engine, select xp_cmdshell.
7. Check the Enable xp_cmdshell checkbox.

8. Click OK.
Alternatively you can also configure XP_CMDSHELL through the proc SP_CONFIGURE.
Enable the ‘show advanced options’ then reconfigure to install it. Once this is done you will be able to enable XP_CMDSHELL. The code is as given.

1. EXEC sp_configure ‘show advanced options’ , 1
   
2. RECONFIGURE
   
3. 
   
4. EXEC sp_configure ‘xp_cmdshell’ , 1
   
5. RECONFIGURE

复制代码

http://awesomesql.wordpress.com/2009/06/11/executing-ssis-command-prompt/