Problem
I use DTEXEC to execute my SSIS packages. I make extensive use of the various command line options available with DTEXEC. The problem I have is that the command lines are unwieldy; they’re just too long and it creates a maintenance headache as it’s so easy to edit a command line and mess it up. I’ve read through a number of your tips about how to utilize the various configuration options for SSIS. Everything I’ve read about the built-in configuration options is that you can retrieve values from a SQL Server table, an XML file, etc. but I really prefer to just use the command line options. I’m currently specifying 10 or more command line options on average. Do you have any ideas on a better way to do this?
Solution
This is a common scenario where you not only want to specify multiple command line options for DTEXEC, but you sometimes need to specify different values each time you run a particular package. One possibility would be to use the DTEXEC command line parameter /Com[mandFile] filespec. This command line parameter allows you to specify the path to a text file that contains a list of command line options that you want to specify for DTEXEC. You simply list the command line options each on a separate line in the text file. You can create as many different command files as you need then specify the appropriate one each time you run your SSIS package. The /Com[mandFile] option allows you to specify any command line option for DTEXEC, which really comes in handy. This will allow you to reduce your DTEXEC command line to: DTEXEC /ComandFile “path to command file goes here”.
Another possibility would be to use the /Conf[igFile] filespec command line option for DTEXEC. This would allow you to specify the path to the XML configuration file to use for package configuration. Since you asked specifically about command line options for DTEXEC, we’ll proceed to walk through an example using the /Com[mandFile] option. For more information about XML package configuration, take a look at our earlier tip Using XML Package Configurations with SSIS.
Example
I once implemented log shipping by using SSIS packages. The customer used log shipping to maintain warm standby servers for disaster recovery and also to offload some reporting from their busy production servers. Restoring transaction logs to the standby servers was performed each hour, then a number of SQL commands would be executed right after the transaction log restores were completed on the standby servers. There were a number of tables populated to drive this processing. Each time the SSIS package was run, there were of course a number of command line parameters specified for DTEXEC. One such parameter was PlanID, a value which specified which row to retrieve to from the LogShippingPlan table. The LogShippingPlan table contained columns like the production server name, standby server name, etc. In the following example I will show how to set the PlanID package variable by specifying the /SET command line parameter in a command file.
The sample SSIS package selects a row from a table where the PlanID is equal to the PlanID package variable which is set from the command file. The selected row is output to an Excel file just for demonstration purposes. The data flow is as follows:
The query in the OLE DB Source is: select * from dbo.logshippingplan where planid = ?
The parameter is replaced by the value of the PlanID package variable. To specify a value for the PlanID package variable on the DTEXEC command line, use the /SET option:
/SET "\Package.Variables[User::PlanID].Properties[Value]";"1"
Note the syntax – you specify the “path” to the PlanID package variable and the property to set is Value. The property to set is separated from the property value by a semi-colon, and both are enclosed in double quotes. You could specify the /SET option as shown on the DTEXEC command line; we’ll put it in a text file and use the /CommandFile option instead.
To find the list of package variables, click on the Package Explorer in your SSIS package:
Create a text file using your favorite text editor, copy the /SET option shown above into the text file, then save it. Assuming the text file was saved to “c:\temp\ssisoptions.txt”, use the following DTEXEC command line to execute a package and set the command line options as specified in the text file; e.g.:
DTEXEC /File "C:\temp\Package.dtsx" /CommandFile "C:\temp\ssisoptions.txt"
Note that our command file only sets one command line option, but you can add as many as you need in this command file. In addition you can create multiple command files each with different option settings, then specify the appropriate one when you run your SSIS package.
Next Steps
- Take a look at the DTEXEC documentation in Books on Line for additional details on the various command line options supported.
- You can download the sample package here to experiment on your own with using the /CommandFile option with DTEXEC.
