SQL Server Integration Services Package Scheduling with SQL Agent

By:   |   Comments (4)   |   Related: > 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. SQLServer2005 Agent SSIS JobStep 1
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). SQLServer2005 Agent SSIS JobStep GeneralTab 2
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. SQLServer2005 Agent SSIS JobStep ConfigurationsTab 3
4 Command Files Tab - Configure environment specific settings for the SSIS Package and reference the file in this interface. SQLServer2005 Agent SSIS JobStep CommandFilesTab 4
5 Data Sources Tab - Select the needed connection managers from the listing of the available connection managers in the SSIS Package. SQLServer2005 Agent SSIS JobStep DataSourcesTab 5
6 Execution Options Tab - Configure the package on validation warnings, package validation and enabling checkpoints within the package. SQLServer2005 Agent SSIS JobStep ExecutionOptionsTab 6
7 Logging Tab - The ability to specify a log provider (text files, Profiler, SQL Server, Windows Event Log, XML file) for the SSIS Package. SQLServer2005 Agent SSIS JobStep LoggingTab 7
8 Set Values Tab - Type in specific properties from the SSIS Package and their associated value. SQLServer2005 Agent SSIS JobStep SetValuesTab 8
9 Verification Tab - Ability to execute only signed packages as well as specific SSIS Package versions. SQLServer2005 Agent SSIS JobStep VerificationTab 9
10 Command Line Tab - The actual command executed based on the selections from the previous interfaces. SQLServer2005 Agent SSIS JobStep CommandLineTab 10
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Jeremy Kadlec 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

Comments For This Article

Tuesday, March 7, 2017 - 8:29:45 AM - damini Back To Top (47480)

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 (30108)

 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 9, 2010 - 11:24:21 AM - agnnga Back To Top (10026)


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 9, 2010 - 11:15:00 AM - agnnga Back To Top (10025)



[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.


get free sql tips
agree to terms