Database (SQL Server)

The SQL Server job steps support various SQL Server database operations.

 

 

This job step allows you to connect to a SQL Server database and establish a handle. That handle can then be passed to many of the other database job steps. The advantage of this approach is that you can perform database-to-database operations concurrently.

 

 

Platforms Supported

Windows.

 

Software Pre-Requisites

Windows: Microsoft SQL Server OLE/DB drivers are used.

 

Job Step Properties

 

ServerName – This property represents the name of the SQL Server machine you want to access. You can click on the dropdown helper to obtain a list of enumerable SQL Server machines (and any applicable instances). If your machine is not present, you can type the name of the machine.

 

Database – This property represents the name of the Database you want to backup. You can click on the dropdown helper to obtain a list of enumerable databases located on the specified SQL Server machine (please note that if the Server is entered as a variable the helper will not be able to assist). If your database is not present, you can type the name of the database.

 

UserAccount – The object path of a User Account object. Clicking on the “Helper” will cause a tree display of all ActiveBatch containers. You may then select a User Account object. The User Account credentials must have proper access to the target database for the backup to work property.

 

Connection Timeout – This property indicates the amount of time, in seconds, that the connection must complete before it will timeout. The default value is 30 seconds.

 

Return Job Step

 

ConnectionHandle – This property represents the connection handle to the database. Use the Disconnect job step to disconnect from the database.

 

 

This job step is used with Connect to disconnect from an established connection. See previous Connect job step for example usage.

 

Platforms Supported

Windows.

 

Software Pre-Requisites

Windows: Microsoft SQL Server OLE/DB drivers are used.

 

Job Step Properties

Connection Handle – This property represents the established connection handle to the database you wish to disconnect.

 

 

This job step supports the backup of a SQL Server database.

 

 

Platforms Supported

Windows.

 

Software Pre-Requisites

Windows: Microsoft SQL Server OLE/DB drivers are used.

 

Job Step Properties

 

ServerName – This property represents the name of the SQL Server machine you want to access. You can click on the dropdown helper to obtain a list of enumerable SQL Server machines (and any applicable instances). If your machine is not present, you can type the name of the machine

 

Database – This property represents the name of the Database you want to backup. You can click on the dropdown helper to obtain a list of enumerable databases located on the specified SQL Server machine (please note that if the Server is entered as a variable the helper will not be able to assist). If your database is not present, you can type the name of the database.

 

FileName – This property represents a valid Windows file specification that will be used to create the database backup.

 

UserAccount – The object path of a User Account object. Clicking on the “Helper” will cause a tree display of all ActiveBatch containers. You may then select a User Account object. The User Account credentials must have proper access to the target database for the backup to work property.

 

Type – This property represents the type of backup to be performed. Three (3) choices are present: Full, Incremental and TransactionLog. A more detailed explanation of the types of backups and what each one performs can be found in the Microsoft SQL Server Administrator’s documentation.

 

CopyOnly – This Boolean property determines whether this backup is marked as Copy-Only. A Copy-Only backup is a SQL Server backup that is independent of any backup strategty being deployed. By default, this property is False.

 

 

This job step allows you to execute a SQL Server DTS package. A DTS (or Data Transformation Service) package allows you to transform data from one database or table to another database or file. The package contains commands to the DTS service which indicate how the transformation is to take place.

 

 

Platforms Supported

Windows.

 

Software Pre-Requisites

Design and Windows: Microsoft SQL Server Integration Services.

 

Job Step Properties

 

ServerName – This property represents the name of the SQL Server machine you want to access. You can click on the dropdown helper to obtain a list of enumerable SQL Server machines (and any applicable instances). If your machine is not present, you can type the name of the machine.

 

UserAccount – The object path of a User Account object. Clicking on the “Helper” will cause a tree display of all ActiveBatch containers. You may then select a User Account object. The User Account credentials must have proper access to the target databases. If a user account is not specified, the default execution user will be used.

 

PackageName – The name of the DTS package.

 

Variables – You may optionally specify parameter value pairs that represent variables used within your DTS package.  In the above example, note that ActiveBatch variables may be freely used as needed.

 

 

This job step allows you to execute a SQL Server Job within the ActiveBatch environment. Running SQL Server Jobs within ActiveBatch allows you to leverage your existing jobs while also removing the limitations present in the SQL Server software (for example, cross-platform workflow, chaining SQL Server machines, etc).

 

 

Platforms Supported

Windows.

 

Software Pre-Requisites

Design and Windows: Microsoft SQL Server Client Tools.

 

Job Step Properties

 

ServerName – This property represents the name of the SQL Server machine you want to access. You can click on the dropdown helper to obtain a list of enumerable SQL Server machines (and any applicable instances). If your machine is not present, you can type the name of the machine.

 

UserAccount – The object path of a User Account object. Clicking on the “Helper” will cause a tree display of all ActiveBatch containers. You may then select a User Account object. The User Account credentials must have proper access to the target databases. If a user account is not specified, the default execution user will be used.

 

JobName – This property represents the name of the SQL Server job you want to execute. You can click on the dropdown helper to obtain a list of enumerable SQL Server jobs. If your job is not present, you can type in the name of the job.

 

WaitCompletion – This Boolean property indicates whether ActiveBatch should wait for the job to complete. A value of true means the ActiveBatch job should wait for SQL Server job completion. The default is true (or to wait).

 

PollingInterval – This property indicates how often ActiveBatch should check on the progress of the SQL Server job. This property is only applicable if WaitCompletion=True. A value of zero (0) indicates that the default ActiveBatch value of 30 seconds will be used.

 

 

This job step allows the execution of a SQL Server SSIS Package. SSIS (or SQL Server Integration Services) is a Microsoft platform for building enterprise-level data integration or data transformation solution. The job step consists of five (5) areas (tabs): General, Configurations, Connection Managers, Variables, and Parameters. The Package Source selected on the General tab determines which of the 4 remaining tabs are enabled.

 

 

Platforms Supported

Windows.

 

Software Pre-Requisites

Design and Windows: Microsoft SQL Server Integration Services.

 

General Job Step Properties

 

PackageSource – This property indicates where the package itself is generally located. Four (4) choices are available: SQLServer, SsisPackageSource, SsisCatalog and FileSystem. SQL Server indicates that the package is stored with the database. SsisPackageSource indicates that the package has been cataloged and can be found on the selected SQL Server machine. FileSystem indicates that the package is located in a file. The dropdown helper provides the possible choices.

 

Server – This property represents the name of the SQL Server machine you want to access. You can click on the dropdown helper to obtain a list of enumerable SQL Server machines (and any applicable instances). If your machine is not present, you can type the name of the machine.

 

UserAccount – The object path of a User Account object. Clicking on the “Helper” will cause a tree display of all ActiveBatch containers. You may then select a User Account object. The User Account credentials must have proper access to the target databases. If a user account is not specified, the default execution user will be used.

 

Package – This property, depending on the PackageSource selection, is either a package location within the SQL Server machine or a file specification. Two buttons are available for the package. Load Package will overwrite any existing package that had been defined to ActiveBatch (the ‘overwrite’ applies to the job step itself and not any SSIS package storage). Update Package is used to update a previously defined package in particular as it relates to connection strings and/or variables.

 

Package Password – This property is required if the package is encoded for additional protection. The property represents a User Account object containing a related password. The Username portion is ignored.

 

Environment – This optional property is only visible when SsisCatalog is selected as the PackageSource. The property can be used if the package has a server environment configured to specify runtime values for packages contained in your project. Click on the Environment property’s ellipsis to select an environment (see the image below, taken from Microsoft’s SSMS). Alternatively, the property can be manually entered by clicking on the variable binding button to the right of the ellipsis.  In this example, if the Dev environment was selected, the Environment property value would be: /SSISDB/QACI/Dev.

 

 

 

Configurations Job Step Properties

 

Configurations – This property governs the configurations present for this SSIS Package. Two buttons are present: Add and Delete All. To add a new configuration click the Add button. A new configuration is made available and you may edit each property as necessary. Delete All deletes all the configurations you’ve defined.

 

Information concerning the Type and other parameters for an SSIS job can be found at: https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.dtsconfigurationtype.aspx.

 

 

Connection Manager Job Step Properties

 

Connections – This property governs the connections present for this SSIS Package. Two buttons are present: Add and Delete All. To add a new connection click the Add button. A new connection is made available and you may edit each property as necessary and required to ensure the proper database is specified. Delete All deletes all the connections you’ve defined.

 

 

Variables Job Step Properties

 

Variables – This property, like Connections, allows you to add or delete variables. Two buttons are present: Add and Delete All.

 

To add a new variable click the Add button. A property called SsisVariable appears with a single method Asci.ActiveBatch.SqlServer.SsisVariable. Select that method and a Name and Value pair will be displayed. You specify the name of the SSIS variable in the Name property and its corresponding value in the Value property.

 

Delete All deletes all the variables you’ve defined.

 

 

When you select an SSIS Catalog package, the Parameters tab becomes active and allows you to specify parameters for the SSIS Catalog job step.

 

 

Parameters is a collection of one or more SSISParameters (these parameters can also include those created as part of the Variables and Connection Manager tabs). Each parameter is a pair of Name/Value corresponding to the parameter loaded from the package.

 

 

 

Platforms Supported

Windows, UNIX-based.

 

Software Pre-Requisites

Windows: Microsoft SQL Server OLE/DB drivers are used.

 

UNIX: JDBC drivers are used as specified (see Installation Manual for drivers tested).

 

Job Step Properties

 

Connection Information – This set of properties indicates how the job step is to connect to the database.

  • Database

    • Database – This property represents the name of the Database you want to access. You can click on the dropdown helper to obtain a list of enumerable databases located on the specified SQL Server machine (please note that if the Server is entered as a variable the helper will not be able to assist). If your database is not present, you can type the name of the database.

    • ServerName – This property represents the name of the SQL Server machine you want to access. You can click on the dropdown helper to obtain a list of enumerable SQL Server machines (and any applicable instances). If your machine is not present, you can type the name of the machine.

    • UserAccount – The object path of a User Account object. Clicking on the “Helper” will cause a tree display of all ActiveBatch containers. You may then select a User Account object. The User Account credentials must have proper database user credentials (not Windows) access to the target databases. If a user account is not specified, the default execution user will be used.

  • Handle – Windows: This property represents a ConnectionHandle from an earlier successful Connect job step.

 

Block – This property represents the block of T-SQL code. You can click on the dropdown to obtain a small multi-line text box suitable for entering your code.

 

Output Results – This Boolean property indicates the level of logging available for this job step. A value of True indicates you want the output results to be logged to the job’s log file. A value of false limits any logging to only errors.

 

Output Messages – This Boolean property (default=False) indicates whether any output should be captured and included as part of the Job log file. In the above figure, the Print statement will be captured if this property is set to True; otherwise it will be ignored. This property is currently only available on Windows-based systems.

 

Timeout – This property indicates the amount of time, in seconds, that the T-SQL block must complete before it will timeout. The default value is 30 seconds.

 

Return Value Options – This property indicates whether the job step should return a Dataset object or not. The default is None. The Dataset object is only currently available on Windows-based systems.

 

Job Step Return

 

Dataset – If the ‘Return Value Option’ is specified as ‘Dataset’, this collection of properties contains information concerning the T-SQL Block execution including record sets.