Adding a Database Job

To add a database job:

  1. Click Definitions > Jobs on the Navigation pane to display the Jobs pane.

  2. Click Add in the TA toolbar or right-click in either the Navigation or Jobs pane and choose Add MSSQL Job from the context menu to display the MSSQL Job Definition dialog.

  3. Click n the MSSql Job Name field, and enter a name up to 50 characters in length for your job.

    The Microsoft SQL Job Name is an identifier for TA only. All of the other job definition information, such as Job Class, Owner and Parent Group, is also the same as a standard TA job and is used in the same way.

    If you are putting your Microsoft SQL job into a group, note that unless the parent group selected has an adapter connection assigned, you must clear the Inherited option on the Run tab before you can click a Microsoft SQL connection.

  4. Choose an adapter name from the Agent/Adapter Name list.

  5. Choose a runtime user from the Runtime User list.

  6. Click the MSSql tab.

  7. Choose Database Job from the Job Type list.

  8. Click the Name field, and enter the name you want to use to identify the job within SQL Server.

    If you would like the SQL job name to match the TA job name, leave this field blank and click the Use Job’s Name option. When this option is selected, the Name field will be disabled.

  9. Choose the SQL Server category to classify job from the Category list. The default is Uncategorized.

  10. Click Start Step list, and then click the first step to execute when job is run.

  11. Use the Import Job field to choose a pre-existing job to import as a template. See Importing Steps from an Existing Database Job.

  12. Click the Enabled option to enable or disable job definition.

  13. Click OK.

  14. Click Add to display the Job Step dialog.

  15. Enter a name for the step in the Step Name field.

  16. Choose the type of step from the Type field. Database jobs support a variety of step types, from ActiveX scripts to T-SQL procedures.

    When you choose the SQL Server Integration Services Package from the Type dropdown list, the Package tab and the Configuration tab are displayed. Choose the package source from the Package Source dropdown list.

    For more information about the supported package source and its configuration, follow SQL Server Agent Jobs for Packages.

  17. Click the database associated with the job from the Database list.

  18. Type in a command to execute in the Command tab field.

    If you are using parameters to be replaced at runtime, click the Parameters tab to view a list of parameters that have been preceded by a colon where you can provide values.

  19. Click the Advanced tab to configure options for this step.

    Note: Depending on the option selected from the Type list of the General tab, this dialog displays different options.

  20. Click Append output to step history option. You can also re-direct the output to a local file or log it to a database table.

    In the advanced configuration, you can also specify what action to take when a step succeeds or fails. You can stop the entire job, reporting either success or failure. Or, you can jump to any previous or next step. For the last step of the job, you usually want to specify Quit the job reporting success from the On success action list.

  21. Click OK to return to the MSSql Job tab.