By: Jeremy Kadlec | Comments (4) | Related: > SQL Server Agent
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:
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
