Schedule a Package by using SQL Server Agent

  • Article

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

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

  2. 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 click New Job.

    • To add a step to an existing job, expand Jobs, right-click the job, and then click Properties.

  3. On the General page, if you are creating a new job, provide a job name, select an owner and job category, and, optionally, provide a job description.

  4. To make the job available for scheduling, select Enabled.

  5. To create a job step for the package you want to schedule, click Steps, and then click New.

  6. Select Integration Services Package for the job step type.

  7. 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 does not run when you call the SSIS package from a SQL Server Agent job step.

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

  8. In the Package Source list box, click 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 Configuring the 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 log on to the server using only a Microsoft Windows user account. SQL Server authentication is not available.
    Package

    Click 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.    		 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. ** 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.

    To enter the literal value, click the ellipsis button next to a parameter. The Edit Literal Value for Execution dialog box appears.

    To use an environment variable, click Environment and then select the environment that contains the variable you want to use.



    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. The Integration Services Project Conversion Wizard enables you to replace package configurations with parameters.

    For information on how to create a server environment and map a variable to a parameter, see Create and Map a Server Environment.
    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. ** 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.

    To enter the literal value, click the ellipsis button next to a parameter. The Edit Literal Value for Execution dialog box appears.

    To use an environment variable, click Environment and then select the environment that contains the variable you want to use.



    For information on how to create a server environment and map a variable to a connection manager property, see Create and Map a Server Environment.
    Advanced

    Located on the Configuration tab.    		 Configure the following additional settings for the package execution.



    Property overrides: Click 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, click a row in the Property overrides box and then click Edit or Remove. Note that 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 2014 Integration Services (SSIS) and deploy to the Integration Services server use parameters instead of configurations. 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.
    Logging level: The logging level you select determines what information is displayed in SSISDB views and in reports for the Integration Services server. Note that selecting the Performance or Verbose logging level may impact the performance of the package execution. Select one of the following logging levels for 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.

    For more information, see Enable Logging for Package Execution on the SSIS Server.
    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 is not 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 log on to 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

    Click 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 click 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, click the connection manager and then click 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 consider 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 options 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 is not 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 are selecting file connection managers that are contained in the package. For the SQL Server log provider, you are 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, click the row and then click 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 is not signed or the signature is not 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 will not 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.

    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 to generate the command line text.

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

  9. Click 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.

  10. To create a schedule for a job step, click 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.

See Also

Execution of Projects and Packages