Debunking the Myths: Cloud HA and DR common misconceptions

Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

DTEXEC Command Line Parameters Using Command Files

MSSQLTips author Ray Barley By:   |   Read Comments (6)   |   Related Tips: 1 | 2 | 3 | 4 | More > Integration Services Execute Package Options

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.


Last Update: 8/10/2009


About the author
MSSQLTips author Ray Barley
Ray Barley is a Principal Architect at RDA Corporation and a MSSQLTips.com BI Expert.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Thursday, November 01, 2012 - 10:46:37 AM - Rick Cloud Read The Tip

Ray, I loved this tip. However, with the use of 64bit machines, can you still use this option of outputing an excel-destiation by specifying /X86    in the commandFile?


Thursday, November 01, 2012 - 12:03:23 PM - sqlfriend Read The Tip

Thanks for the tip, I have th same questoin as Rick.


Thursday, November 01, 2012 - 3:30:30 PM - Ray Barley Read The Tip

According to http://msdn.microsoft.com/en-us/library/hh231187.aspx the answer is no

x86 Causes SQL Server Agent to run the package in 32-bit mode on a 64-bit computer. This option is set by SQL Server Agent when the following conditions are true:

  • The job step type is SQL Server Integration Services package.

  • The Use 32 bit runtime option on the Execution options tab of the New Job Step dialog box is selected.

You can also set this option for a SQL Server Agent job step by using stored procedures or SQL Server Management Objects (SMO) to programmatically create the job. 

This option is only used by SQL Server Agent. This option is ignored if you run the dtexec utility at the command prompt.

 

However you can execute the 32 bit version of DTEXEC; e.g. C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\DTEXEC.EXE


Monday, November 19, 2012 - 10:36:50 AM - Rick Cloud Read The Tip

Ray,

I've looked at my "Program Files(x86)\Microsoft SQL Server\" and I don't have a folder '110'...  I have an 80, a 90, and a 100

This is true for my "Program Files\Microsoft SQL Server\" as well. However, the one in (X86) is 54KB and the other is 64KB...

Is the one in (X86) the 32bit version?  They both appear to have the same 'version #' 2009.100.1600.1

What I'm trying to do, is this:

Using MS Workflow to call an ssis package to create an excel file from a stored procedure...


Monday, November 19, 2012 - 3:38:00 PM - Ray Barley Read The Tip

The 110 folder is for SQL Server 2012.

You will find the 32 bit version of DTEXEC underneath the (x86) folder; e.g. DTS\Binn

You will have to make sure that the folder is in your PATH

 


Monday, November 19, 2012 - 6:00:33 PM - Rick Cloud Read The Tip

Ray, thanks. I'll try it.



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.