My "SSIS and SQL Server Agent" post is the most frequently accessed post on my blog, or at least it has been for the two months that I’ve actually been tracking site usage. But some recent posts on the SSIS Forums on MSDN have made me realize that it was probably long overdue for a follow-up focused choosing the correct job step type.
Microsoft has provided two different SQL Server Agent job step types that you can use to execute SQL Server Integration Services Packages:
- The "SQL Server Integration Services Package" job step type
- The "Operating system (CmdExec)" job step type.
Now which one leaps out at you as being ideal for running SQL Server Integration Services packages?
Unfortunately, despite the name and the oh-so-friendly GUI through which you can specify all sorts of options, the SQL Server Integration Services Package job step type is not the one you want to use. There are quite a few reasons I believe this (and it’s worth pointing out that while there are people who agree with me, there are some very talented SSIS people out there who do not) including:
- Consistency: You can create a batch file that calls DTEXEC, test that execution outside of SQL Server Agent, and then copy and paste the DTEXEC command line into the SQL Server Agent job. This minimizes the number of environmental variables that change between testing package execution outside of SQL Server Agent and testing package execution within a SQL Server Agent job.
- Discoverability: I realize that this won’t apply to everyone, but I personally find it much simpler to look at command line switches and options than to look at nine different tabs in a tabbed window. Looking at the command line I know that I’m seeing everything. Looking through the tabs, I’m pretty sure I’m going to miss something.
- Troubleshooting and Error Output: This is the big one. When you run a package through the SQL Server Integration Services Package job step type, you’re limited to the error logging that is already configured within the package itself, and this isn’t always sufficient to diagnose and resolve problems with the job. But when you execute a package through DTEXEX and the the CmdExec job step type, you have more options that make tracking down and resolving problems much simpler.
So how do you do it? And do you really want to, if you’re not one of those weird people who would rather have a command line than a GUI?
Well, the answer to the second question here is yes. Yes you do. Because even though you’re using the CmdExec job step type, you still have a GUI to use. And here’s how.
- Double-click on the package you want to run. This will open up DTEXECUI, the Execute Package Utility.
- In DTEXECUI, specify all of the options that you need by using its nice friendly GUI.
- On the Command Line tab in DTEXECUI, highlight and copy the Command line text – these are the switches and options that need to be specified for DTEXEC to duplicate the options you selected in the GUI:
- In you SQL Server Agent job, add a new job step, and specify the job step type as CmdExec. In the Command text box, type DTEXEC and a space, and then paste the command line text that you copied from DTEXECUI earlier:
- On the advanced tab of the New Job Step dialog box, enter a file path and name in the Output File text box. (This is the big thing.) With this specified, the output from DTEXEC, which is exactly what you want when troubleshooting problems, will be written to a text file. You can also specify whether the file should be overwritten or appended to with each execution.
Now, when you execute your SQL Server Agent job, all DTEXEC output will be written to this text file. And if something goes wrong, you now have a single, easy to use location for tracking down and resolving the problems.
One of the most common things that the MVPs and other people who help out on the SSIS forums request is "the complete set of error messages and warnings from the package execution." The reason we ask for this is that it’s pretty much impossible to remotely troubleshoot someone else’s problems without complete and explicit output. And the greatest thing about using the CmdExec job step type is that all of this information is now in one place. Copy and paste the errors and warnings, and help is (hopefully) on your way.
 Not Windows environment variables – I’m talking here about "things that are important to your deployment environment."
 If your package is stored in SQL Server and not on the file system, you can still use this basic technique, but you will have to browse to and select the package from within DTEXECUI, as opposed to double-clicking on the package on the file system.
 You can also specify the fully qualified path to DTEXEC.EXE – this is helpful if DTEXEC is not on the path, or if you’re on a 64-bit server and want to explicitly select the 32- or 64-bit version of DTEXEC for this package.
 And we’re not the only ones who ask for this. Your in-house support folks will probably ask for the same thing if you have a support team who works with SSIS.