Blog: SQL Server Tip of the Day - SSIS & the SQL Job (tags: #SqlServer, #SqlAgent, #SSIS, #SQLjobs
Running an SSIS package via the SQL Server Agent jobs can be an entertaining experience when said package errors out. It gets even worse when the DBA in question uses an Integration Services job step to run the package. I don't know about anyone else, but I've found that the IS job step is only useful for coming up with the syntax necessary to run the job. It gives generic error messages that don't assist at all in the troubleshooting process.
My solution? Run the SSIS package in an Operating System job step. Use the Integration Services job step to find the package and the config file, the flip over to Command. Copy the script (just in case it disappears) and then flip the job step type to Operating System. Change the /SQL to /DTS (only necessary in SQL 2005. SQL 2008 has /DTS) and add "dtexec.exe" without the quotes at the front of the script. If any of your script references a hard drive letter (i.e., "E:\"), change the script to reflect a URL address instead (i.e., \\MyServer\E$\"). Then verify your package location isn't missing the top folder ("\MSDB" or "\File System") at the beginning of the path name.
In the Advanced tab of this job step, add a text file location where the package will log to. Make sure to click "Include Step Details" so that you get everything you can. Whether you append or overwrite the error file is your choice. But including the details in your text log will give you a much more expressive version of the errors your SSIS package throws than just the Integration Services step alone.
My solution? Run the SSIS package in an Operating System job step. Use the Integration Services job step to find the package and the config file, the flip over to Command. Copy the script (just in case it disappears) and then flip the job step type to Operating System. Change the /SQL to /DTS (only necessary in SQL 2005. SQL 2008 has /DTS) and add "dtexec.exe" without the quotes at the front of the script. If any of your script references a hard drive letter (i.e., "E:\"), change the script to reflect a URL address instead (i.e., \\MyServer\E$\"). Then verify your package location isn't missing the top folder ("\MSDB" or "\File System") at the beginning of the path name.
In the Advanced tab of this job step, add a text file location where the package will log to. Make sure to click "Include Step Details" so that you get everything you can. Whether you append or overwrite the error file is your choice. But including the details in your text log will give you a much more expressive version of the errors your SSIS package throws than just the Integration Services step alone.