Problem
With the new features in SQL Server 2005 Integration Services, what are the related impacts to SQL Server 2005 Agent? Do I still need to build a dtsrun command (SQL Server 2000) to execute an SSIS Package in an automated manner? What insight do I have into the internal SSIS objects and some of the advanced features?
Solution
The value with SQL Server Agent and SSIS is the integration between the 2 technologies. The core SSIS features are exposed directly via SQL Server Agent’s Job Step Type of ‘SQL Server Integration Services Package.’ This Job Type option provides the following interfaces into interact with the SSIS Package in a more straightforward manner:
- SQL Server Integration Services Package Job Step
- General Tab
- Configurations Tab
- Command Files Tab
- Data Sources Tab
- Execution Options Tab
- Logging Tab
- Set Values Tab
- Verification Tab
- Command Line Tab
Below outlines the individual interfaces with a description and sample screen shot:
| ID | Description | Screen Shot |
| 1 | SQL Server Integration Services Package Job Step – This SSIS interface can be accessed via SQL Server Agent when a new job is created and the Job Step Type is SQL Server Integration Services Package. |  |
| 2 | General Tab – Specify the package source (SQL Server, File System, SSIS Package Store), SQL Server with the SSIS Package and authentication (Windows or SQL Server). |  |
| 3 | Configurations Tab – Specify a alternative configurations file from the default stored at C:\Program Files\Microsoft SQL Server\90\DTS\Binn\ MsDtsSrvr.ini.xml. |  |
| 4 | Command Files Tab – Configure environment specific settings for the SSIS Package and reference the file in this interface. |  |
| 5 | Data Sources Tab – Select the needed connection managers from the listing of the available connection managers in the SSIS Package. |  |
| 6 | Execution Options Tab – Configure the package on validation warnings, package validation and enabling checkpoints within the package. |  |
| 7 | Logging Tab – The ability to specify a log provider (text files, Profiler, SQL Server, Windows Event Log, XML file) for the SSIS Package. |  |
| 8 | Set Values Tab – Type in specific properties from the SSIS Package and their associated value. |  |
| 9 | Verification Tab – Ability to execute only signed packages as well as specific SSIS Package versions. |  |
| 10 | Command Line Tab – The actual command executed based on the selections from the previous interfaces. |  |
Next Steps
- With SQL Server Agent many new features are available to better integrate with the remainder of the SQL Server 2005 suite of products. As you begin to work with these new technologies be sure to keep SQL Server Agent in the back of your mind as it pertains to deployment.
- We have just scratched the surface with the new Job Types, check out the others as your projects move to Replication, Analysis Services, etc.
- For additional information, reference the following items:
Jeremy Kadlec is a Founder, Editor and Author at MSSQLTips.com with more than 300 contributions and 25+ years of SQL Server experience. Jeremy leads a team of more than 300 authors helping millions of SQL Server professionals around the globe every second of the day for the last 20 years. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP based on his community contributions. Jeremy brings 25+ years of SQL Server DBA and Developer knowledge to the community and holds a bachelor’s degree from SSU and master’s degree from UMBC.
