SQL Server Integration Services Package Scheduling with SQL Agent

By:   |   Updated: 2007-02-15   |   Comments (4)   |   Related: More > SQL Server Agent


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?


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

Last Updated: 2007-02-15

get scripts

next tip button

About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is the Co-Founder, Editor and Author at MSSQLTips.com, CTO @ Edgewood Solutions and a six time SQL Server MVP.

View all my tips
Related Resources

More SQL Server Solutions

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

Tuesday, March 07, 2017 - 8:29:45 AM - damini Back To Top

Hi Jeremy,


How can we dynamically set the values for SSIS package properties in Set values tab instead of hardcoded ones?





Friday, April 18, 2014 - 5:07:59 PM - svasa Back To Top

 Hi Jeremy,


I'm unable to see Job-->Steps: Data source information when i connect Named instance from my local machine. I'm running SSIS packages using SQL JOb(SQL 2008R2 developer Edition), When RDP the Instance I'm able to see the datasource info. when i connceted through my local SSMS I'm not able to see. I'm SA and i created a Proxy Account also still no luck, please advice...

Thank you


Srinivas Vasa 

Monday, August 09, 2010 - 11:24:21 AM - agnnga Back To Top


Well, I think that there was a bug in the last post …


Hello again,

I have created a package which is launched by the sqlserver agent. It worked well until I changed the password for my login to the server ( I have no idea whether it has any impact on the package or not??). The message that I’ve got is about these:


[165] Erreur ODBC : Time waiting for the connection expired

[298] Error SQLServer: Error from the network or an instance, being created during the SQL Server connection establishement. Can’t find or access to the server.

[000] Can’t connect to the server « local ». SQLServer Agent can’t start


The words might not be the same as they are sent by SQLServer because I had to translate them in English J But if there is anything that is not clear, please tell me and I’ll try my best to … explain it in another way!


Would you help me fix this problem?

Here are some more details that might be helpful :

-         I am member of sysadmin

-         There are more than 1 instance on the server

-         My account/the package has access to all the distant database that are used/called in the package


Thank you in advance for your suggestion.

Monday, August 09, 2010 - 11:15:00 AM - agnnga Back To Top



[165] Erreur ODBC : Time waiting for the connection expired

[298] Error SQLServer: Error from the network or an instance, being created during the SQL Server connection establishement. Can’t find or access to the server.



Recommended Reading

Querying SQL Server Agent Job Information

Run SQL Server Agent Job from SSIS

Querying SQL Server Agent Job History Data

Running a SSIS Package from SQL Server Agent Using a Proxy Account

SQL Server Agent Job Schedule Reporting

get free sql tips
agree to terms

Learn more about SQL Server tools