DTEXEC Command Line Parameters Using Command Files

By:   |   Updated: 2009-08-10   |   Comments (8)   |   Related: 1 | 2 | 3 | 4 | More > Integration Services Execute Package Options


   Free MSSQLTips whitepaper - "Understanding Windows Server Cluster Quorum 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:

data flow

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:

package explorer

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.



Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights




get scripts

next tip button



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

View all my tips


Article Last Updated: 2009-08-10

Comments For This Article




Friday, January 14, 2022 - 11:47:08 AM - RAYMOND BARLEY Back To Top (89665)
I have never run SSIS on linux.

To me the error message is saying you're running DTEXEC on linux, the EXECUTE PROCESS task has found the executable, but the executable is not a linux executable. If you manually run the executable on your linux machine you should get the same error message.

Friday, January 14, 2022 - 8:43:05 AM - Sharkzz10 Back To Top (89662)
I am migrating SSIS ETL from Windows to Linux and using dtexec. In Windows, the executables can be added as C:\..\...\python.exe. I would like to know how the same can be migrated to Linux as python3 will be present in /usr/bin/python3.

I have tried adding paths like C:\usr\bin\python3 as SSIS on Linux maps the Windows-style path C:\test to the Linux-style path /test. But it didn't work.

The error is caused by this command:

/opt/ssis/bin/dtexec /Pack xxx.dtsx /Proj xxx.ispac /decrypt test
Error: 2022-01-13 20:10:42.83 Code: 0xC002F304 Source: DDL Execute Process Task Description: An error occurred with the following error message: "The specified executable is not a valid application for this OS platform.".

Monday, November 19, 2012 - 6:00:33 PM - Rick Cloud Back To Top (20419)

Ray, thanks. I'll try it.


Monday, November 19, 2012 - 3:38:00 PM - Ray Barley Back To Top (20417)

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 - 10:36:50 AM - Rick Cloud Back To Top (20411)

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...


Thursday, November 1, 2012 - 3:30:30 PM - Ray Barley Back To Top (20179)

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


Thursday, November 1, 2012 - 12:03:23 PM - sqlfriend Back To Top (20177)

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


Thursday, November 1, 2012 - 10:46:37 AM - Rick Cloud Back To Top (20174)

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?



download














get free sql tips
agree to terms