SQL Server Agent Jobs for Packages

Article
11/18/2022

Applies to: SQL Server SSIS Integration Runtime in Azure Data Factory

You can automate and schedule the execution of SQL Server Integration Services packages by using SQL Server Agent. You can schedule packages that are deployed to the Integration Services server, and are stored in SQL Server, the SSIS Package Store, and the file system.

Note

This article describes how to schedule SSIS packages in general, and how to schedule packages on premises. You can also run and schedule SSIS packages on the following platforms:

The Microsoft Azure cloud. For more info, see Lift and shift SQL Server Integration Services workloads to the cloud and Schedule the execution of an SSIS package in Azure.
Linux. For more info, see Extract, transform, and load data on Linux with SSIS and Schedule SQL Server Integration Services package execution on Linux with cron.

Scheduling Jobs in SQL Server Agent

SQL Server Agent is the service installed by SQL Server that lets you automate and schedule tasks by running SQL Server Agent jobs. The SQL Server Agent service must be running before jobs can run automatically. For more information, see Configure SQL Server Agent.

The SQL Server Agent node appears in Object Explorer in SQL Server Management Studio when you connect to an instance of the SQL Server Database Engine.

To automate a recurring task, you create a job by using the New Job dialog box. For more information, see Implement Jobs.

After you create the job, you must add at least one step. A job can include multiple steps, and each step can perform a different task. For more information, see Manage Job Steps.

After you create the job and the job steps, you can create a schedule for running the job. However you can also create an unscheduled job that you run manually. For more information, see Create and Attach Schedules to Jobs.

You can enhance the job by setting notification options, such as specifying an operator to send an e-mail message to when the job finishes, or adding alerts. For more information, see Alerts.

Scheduling Integration Services Packages

When you create a SQL Server Agent job to schedule Integration Services packages, you must add at least one step and set the type of the step to SQL Server Integration Services Package. A job can include multiple steps, and each step can run a different package.

Running an Integration Services package from a job step is like running a package by using the dtexec (dtexec.exe) and DTExecUI (dtexecui.exe) utilities. Instead of setting the run-time options for a package by using command-line options or the Execute Package Utility dialog box, you set the run-time options in the New Job Step dialog box. For more information about the options for running a package, see dtexec Utility.

For more information, see Schedule a Package by using SQL Server Agent.

For a video that demonstrates how to use SQL Server Agent to run a package, see the video home page, How to: Automate Package Execution by Using the SQL Server Agent (SQL Server Video), in the MSDN Library.

Troubleshooting

A SQL Server Agent job step may fail to start a package even though the package runs successfully in SQL Server Data Tools (SSDT) and from the command line. There are some common reasons for this issue and several recommended solutions. For more information, see the following resources.

Microsoft Knowledge Base article, An SSIS package doesn't run when you call the SSIS package from a SQL Server Agent job step
Video, Troubleshooting: Package Execution Using SQL Server Agent (SQL Server Video), in the MSDN Library.

After a SQL Server Agent job step starts a package, the package execution may fail, or the package may run successfully but with unexpected results. You can use the following tools to troubleshoot these issues.

For packages that are stored in the SQL Server MSDB database, the SSIS Package Store, or in a folder on your local machine, you can use the Log File Viewer and any logs and debug dump files that were generated during the execution of the package.

To use the Log File Viewer, do the following.
1. Right-click the SQL Server Agent job in Object Explorer and then select View History.
2. Locate the job execution in the Log file summary box with the job failed message in the Message column.
3. Expand the job node, and select the job step to view the details of the message in the area below the Log file summary box.
For packages that are stored in the SSISDB database, you can also use the Log File Viewer and any logs and debug dump files that were generated during the execution of the package. In addition, you can use the reports for the Integration Services server.

To find information in the reports for the package execution associated with a job execution, do the following.
1. Follow the steps above to view the details of the message for the job step.
2. Locate the Execution ID listed in the message.
3. Expand the Integration Services Catalog node in Object Explorer.
4. Right-click SSISDB, point to Reports, then Standard Reports, and then select All Executions.
5. In the All Executions report, locate the Execution ID in the ID column. Select Overview, All Messages, or Execution Performance to view information about this package execution.
For more information about the Overview, All Messages, and Execution Performance reports, see Reports for the Integration Services Server.

Schedule a Package by using SQL Server Agent

The following procedure provides steps to automate the execution of a package by using a SQL Server Agent job step to run the package.

To automate package execution by using SQL Server Agent

In SQL Server Management Studio, connect to the instance of SQL Server on which you want to create a job, or the instance that contains the job to which you want to add a step.
Expand the SQL Server Agent node in Object Explorer and perform one of the following tasks:
- To create a new job, right-click Jobs and then select New Job.
- To add a step to an existing job, expand Jobs, right-click the job, and then select Properties.
On the General page, if you're creating a new job, provide a job name, select an owner and job category, and, optionally, provide a job description.
To make the job available for scheduling, select Enabled.
To create a job step for the package you want to schedule, select Steps, and then select New.
Select Integration Services Package for the job step type.
In the Run as list, select SQL Server Agent Service Account or select a proxy account that has the credentials that the job step will use. For information about creating a proxy account, see Create a SQL Server Agent Proxy.

Using a proxy account instead of the SQL Server Agent Service Account may resolve common issues that can occur when executing a package using the SQL Server Agent. For more information about these issues, see the Microsoft Knowledge Base article, An SSIS package doesn't run when you call the SSIS package from a SQL Server Agent job step.
- When running job with a Proxy, one has to have the following security items in place for the job to successfully run.
  
  Credential Login used by the Proxy, the account running the SQL Server Agent and the account running the SQL Server Service require the following permissions:
  - Local Security Policy Attribute: Replace a Process Level Token
  - Full control over %SYSTEMROOT%\Temp
  Failure to put in the security items will result in the job failing and an error message similar to the following: The job failed. A required privilege isn't held by the client.
  
  Note
  
  If the password changes for the credential that the proxy account uses, you need to update the credential password. Otherwise, the job step will fail.
  
  For information about configuring the SQL Server Agent service account, see Set the Service Startup Account for SQL Server Agent (SQL Server Configuration Manager).

In the Package Source list box, select the source of the package and then configure the options for the job step.

The following table describes the possible package sources.

Package Source	Description
SSIS Catalog	Packages that are stored in the SSISDB database. The packages are contained in Integration Services projects that are deployed to the Integration Services server.
SQL Server	Packages that are stored in the MSDB database. You use the Integration Services service to manage these packages.
SSIS Package Store	Packages that are stored in the default folder on your computer. The default folder is <drive>:\Program Files\Microsoft SQL Server\110\DTS\Packages. You use the Integration Services service to manage these packages. Note: You can specify a different folder or specify additional folders in the file system to be managed by the Integration Services service, by modifying the configuration file for Integration Services. For more information, see Integration Services Service (SSIS Service).
File System	Packages that are stored in any folder on your local machine.

The following tables describe the configuration options that are available for the job step depending on the package source you select.

Important

If the package is password-protected, when you click any of the tabs on the General page of the New Job Step dialog box, with the exception of the Package tab, you need to enter the password in the Package Password dialog box that appears. Otherwise the SQL Server Agent job will fail to run the package.

Package Source: SSIS Catalog

Tab	Options
Package	Server Type or select the name of the database server instance that hosts the SSISDB catalog. When SSIS Catalog is the package source, you can sign into the server using only a Microsoft Windows user account. SQL Server authentication isn't available.
	Package Select the ellipsis button and select a package. You are selecting a package in a folder under the Integration Services Catalogs node in Object Explorer.
Parameters Located on the Configuration tab.	The Integration Services Project Conversion Wizard enables you to replace package configurations with parameters. The Parameters tab displays parameters that you added when you designed the package, for example by using SQL Server Data Tools (SSDT). The tab also displays parameters that were added to the package when you converted the Integration Services project from the package deployment model to the project deployment model. Enter new values for parameters that are contained in the package. You can enter a literal value or use the value contained in a server environment variable that you have already mapped to the parameter. To enter the literal value, select the ellipsis button next to a parameter. The Edit Literal Value for Execution dialog box appears. To use an environment variable, select Environment and then select the environment that contains the variable you want to use. Important If you have mapped multiple parameters and/or connection manager properties to variables contained in multiple environments, SQL Server Agent displays an error message. For a given execution, a package can execute only with the values contained in a single server environment. For information on how to create a server environment and map a variable to a parameter, see Deploy Integration Services (SSIS) Projects and Packages.
Connection Managers Located on the Configuration tab.	Change values for connection manager properties. For example, you can change the server name. Parameters are automatically generated on the SSIS server for the connection manager properties. To change a property value, you can enter a literal value or use the value contained in a server environment variable that you have already mapped to the connection manager property. To enter the literal value, select the ellipsis button next to a parameter. The Edit Literal Value for Execution dialog box appears. To use an environment variable, select Environment and then select the environment that contains the variable you want to use. Important If you have mapped multiple parameters and/or connection manager properties to variables contained in multiple environments, SQL Server Agent displays an error message. For a given execution, a package can execute only with the values contained in a single server environment. For information on how to create a server environment and map a variable to a connection manager property, see Deploy Integration Services (SSIS) Projects and Packages.
Advanced Located on the Configuration tab.	Configure the following additional settings for the package execution:
	Property overrides: Select Add to enter a new value for a package property, specify the property path, and indicate whether the property value is sensitive. The Integration Services server encrypts sensitive data. To edit or remove the settings for a property, select a row in the Property overrides box, and then select Edit or Remove. You can find the property path by doing one of the following: -Copy the property path from the XML configuration file (.dtsconfig) file. The path is listed in the Configuration section of the file, as a value of the Path attribute. The following is an example of the path for the MaximumErrorCount property: \Package.Properties[MaximumErrorCount] -Run the Package Configuration Wizard* and copy the property paths from the final Completing the Wizard page. You can then cancel the wizard. Note: The Property overrides option is intended for packages with configurations that you upgraded from a previous release of Integration Services. Packages that you create using SQL Server 2019 Integration Services (SSIS) and deploy to the Integration Services server use parameters instead of configurations.
	Logging level Select one of the following logging levels for the package execution. Selecting the Performance or Verbose logging level may impact the performance of the package execution. None: Logging is turned off. Only the package execution status is logged. Basic: All events are logged, except custom and diagnostic events. This is the default value for the logging level. Performance: Only performance statistics, and OnError and OnWarning events, are logged. Verbose: All events are logged, including custom and diagnostic events. The logging level you select determines what information is displayed in SSISDB views and in reports for the Integration Services server. For more information, see Integration Services (SSIS) Logging.
	Dump on errors Specify whether debug dump files are generated when any error occurs during the execution of the package. The files contain information about the execution of the package that can help you troubleshoot issues. When you select this option, and an error occurs during execution, Integration Services creates a .mdmp file (binary file) and a .tmp file (text file). By default, Integration Services stores the files in the <drive>:\Program Files\Microsoft SQL Server\110\Shared\ErrorDumps folder.
	32-bit runtime Indicate whether to run the package using the 32-bit version of the dtexec utility on a 64-bit computer that has the 64-bit version of SQL Server and SQL Server Agent installed. You may need to run the package using the 32-bit version of dtexec if, for example, your package uses a native OLE DB provider that isn't available in a 64-bit version. For more information, see 64 bit Considerations for Integration Services. By default, when you select the SQL Server Integration Services Package job step type, SQL Server Agent runs the package using the version of the dtexec utility that is automatically invoked by the system. The system invokes either the 32-bit or 64-bit version of the utility depending on the computer processor, and the version of SQL Server and SQL Server Agent that is running on the computer.

Package Source: SQL Server, SSIS Package Store, or File System

Many of the options that you can set for packages stored in SQL Server, the SSIS Package Store, or the file system, correspond to command-line options for the dtexec command prompt utility. For more information about the utility and command-line options, see dtexec Utility.

Tab	Options
Package These are the tab options for packages that are stored in SQL Server or the SSIS Package Store.	Server Type or select the name of the database server instance for SQL Server or the Integration Services service.
	Use Windows Authentication Select this option to sign into the server using a Microsoft Windows user account.
	Use SQL Server Authentication When a user connects with a specified login name and password from a non-trusted connection, SQL Server performs the authentication by checking to see if a SQL Server login account has been set up and if the specified password matches the one previously recorded. If SQL Server cannot find the login account, authentication fails, and the user receives an error message.
	User Name
	Password
	Package Select the ellipsis button and select the package. You are selecting a package in a folder under the Stored Packages node in Object Explorer.
Package These are the tab options for packages that are stored in the file system.	Package Type the full path for the package file, or select the ellipsis button to select the package.
Configurations	Add an XML configuration file to run the package with a specific configuration. You use a package configuration to update the values of package properties at runtime. This option corresponds to the /ConfigFile option for dtexec. To understand how package configurations are applied, see Package Configurations. For information on how to create a package configuration, see Create Package Configurations.
Command files	Specify additional options you want to run with dtexec, in a separate file. For example, you can include a file that contains the /Dump errorcode option, to generate debug dump files when one or more specified events occur while the package is running. You can run a package with different sets of options by creating multiple files and then specifying the appropriate file by using the Command files option. The Command files option corresponds to the /CommandFile option for dtexec.
Data Sources	View the connection managers contained in the package. To modify a connection string, select the connection manager and then select the connection string. This option corresponds to the /Connection option for dtexec.
Execution Options	Fail the package on validation warnings Indicates whether a warning message is considered an error. If you select this option and a warning occurs during validation, the package will fail during validation. This option corresponds to the /WarnAsError option for dtexec. Validate package without executing Indicates whether the package execution is stopped after the validation phase without actually running the package. This option corresponds to the /Validate option for dtexec. Override MacConcurrentExecutables property Specifies the number of executable files that the package can run concurrently. A value of -1 means that the package can run a maximum number of executable files equal to the total number of processors on the computer executing the package, plus two. This option corresponds to the /MaxConcurrent option for dtexec. Enable package checkpoints Indicates whether the package will use checkpoints during package execution. For more information, see Restart Packages by Using Checkpoints. This option corresponds to the /CheckPointing option for dtexec. Override restart options Indicates whether a new value is set for the CheckpointUsage property on the package. Select a value from the Restart option list box. This option corresponds to the /Restart option for dtexec. Use 32 bit runtime Indicate whether to run the package using the 32-bit version of the dtexec utility on a 64-bit computer that has the 64-bit version of SQL Server and SQL Server Agent installed. You may need to run the package using the 32-bit version of dtexec if, for example, your package uses a native OLE DB provider that isn't available in a 64-bit version. For more information, see 64 bit Considerations for Integration Services. By default, when you select the SQL Server Integration Services Package job step type, SQL Server Agent runs the package using the version of the dtexec utility that is automatically invoked by the system. The system invokes either the 32-bit or 64-bit version of the utility depending on the computer processor, and the version of SQL Server and SQL Server Agent that is running on the computer.
Logging	Associate a log provider with the execution of the package. SSIS log provider for Text files Writes log entries to ASCII text files SSIS log provider for SQL Server Writes log entries to the sysssislog table in the MSDB database. SSIS log provider for SQL Server Profiler Writes traces that you can view using SQL Server Profiler. SSIS log provider for Windows Event Log Writes log entries to the Application log in the Windows Event log. SSIS log provider for XML files Writes log files to an XML file. For the text file, XML file, and the SQL Server Profiler log providers, you're selecting file connection managers that are contained in the package. For the SQL Server log provider, you're selecting an OLE DB connection manager that is contained in the package. This option corresponds to the /Logger option for dtexec.
Set values	Override a package property setting. In the Properties box, enter values in the Property Path and Value columns. After you enter values for one property, an empty row appears in the Properties box to enable you to enter values for another property. To remove a property from the Properties box, select the row and then select Remove. You can find the property path by doing one of the following: -Copy the property path from the XML configuration file (.dtsconfig) file. The path is listed in the Configuration section of the file, as a value of the Path attribute. The following is an example of the path for the MaximumErrorCount property: \Package.Properties[MaximumErrorCount] -Run the Package Configuration Wizard* and copy the property paths from the final Completing the Wizard page. You can then cancel the wizard.
Verification	Execute only signed packages Indicates whether the package signature is checked. If the package isn't signed or the signature isn't valid, the package fails. This option corresponds to the /VerifySigned option for dtexec. Verify Package build Indicates whether the build number of the package is verified against the build number that is entered in the Build box next to this option. If a mismatch occurs, the package won't execute. This option corresponds to the /VerifyBuild option for dtexec. Verify package ID Indicates whether the GUID of the package is verified, by comparing it to the package ID that is entered in the Package ID box next to this option. This option corresponds to the /VerifyPackageID option for dtexec. Verify version ID Indicates whether the version GUID of the package is verified, by comparing it version ID that is entered in the Version ID box next to this option. This option corresponds to the /VerifyVersionID option for dtexec.
Command line	Modify the command line options for dtexec. For more information about the options, see dtexec Utility. Restore the original options Use the command-line options that you have set in the Package, Configurations, Command files, Data sources, Execution options, Logging, Set values, and Verification tabs of the Job Set Properties dialog box. Edit the command manually Type additional command-line options in the Command line box. Before you select OK to save your changes to the job step, you can remove all of the additional options that you've typed in the Command line box by clicking Restore the original options. Tip You can copy the command line to a Command Prompt window, add `dtexec`, and run the package from the command line. This is an easy way to generate the command line text.

Select OK to save the settings and close the New Job Step dialog box.

Note

For packages that are stored in the SSIS Catalog, the OK button is disabled when there is an unresolved parameter or connection manager property setting. An unresolved setting occurs when you are using a value contained in a server environment variable to set the parameter or property and one of the following conditions is met.:

The Environment checkbox on the Configuration tab is not selected.

The server environment that contains the variable is not selected in the list box on the Configuration tab.
To create a schedule for a job step, select Schedules in the Select a page pane. For information on how to configure a schedule, see Schedule a Job.

Tip

When you name the schedule, consider using a name that is unique and descriptive so you can more easily distinguish the schedule from other SQL Server Agent schedules.

External Resources

Knowledge Base article, An SSIS package doesn't run when you call the SSIS package from a SQL Server Agent job step, on the Microsoft Web site
Video, Troubleshooting: Package Execution Using SQL Server Agent (SQL Server Video), in the MSDN Library
Video, How to: Automate Package Execution by Using the SQL Server Agent (SQL Server Video), in the MSDN Library
Technical article, Checking SQL Server Agent jobs using Windows PowerShell, on mssqltips.com
Technical article, Auto alert for SQL Agent jobs when they are enabled or disabled, on mssqltips.com
Blog entry, Configuring SQL Agent Jobs to Write to Windows Event Log, on mssqltips.com.