TECH SUPPORT

Automation Schedule- Databases

Follow

Execute SQL Script (from file)

image1125.jpg

 

ODBC Datasource Name:  Drop down and select a DSN.  

 

We strongly recommend System DSNs so that the DSN is visible to MARS even when the user associated with the DSN is logged off.

 

All communication to databases (including Access databases) is done through ODBC, so you must setup a system DSN to the database before you can use this facility.

 

When setting up DSNs we recommend (if possible) that you use Windows Authentication (Trusted Connection).  This ensures that your username and password are not required to be stored in MARS (more secure) and overcomes a large number of security restrictions which Windows places on DSNs.   When using Windows Authentication, make sure that the MARS NT service user (or background application service user) has full rights to the database otherwise, though you will connect when you are logged in, automated scheduling will fail if the Service user has not got rights to the database.

 

When setting up a DSN to a SQL server, you must ensure that the "Default Database" setting is set to the database you are connecting to (Windows defaults this to "Master").

 

UserID:  Enter the User ID MARS should use to log on to the database.

 

Password:  Enter the password associated with the above user.

 

Connect:  Click Connect to connect to the Database.

 

Script Location:  Browse to locate the script.

 

Click OK when done.

 

Update a Database Record

image1126.jpg

 

DSN Name:  Drop down and select a DSN.  

  • We strongly recommend System DSNs so that the DSN is visible to MARS even when the user associated with the DSN is logged off.  

  • All communication to databases (including Access databases) is done through ODBC, so you must setup a system DSN to the database before you can use this facility.

  • When setting up DSNs we recommend (if possible) that you use Windows Authentication (Trusted Connection).  This ensures that your username and password are not required to be stored in MARS (more secure) and overcomes a large number of security restrictions which Windows places on DSNs.   When using Windows Authentication, make sure that the MARS NT service user (or background application service user) has full rights to the database otherwise, though you will connect when you are logged in, automated scheduling will fail if the Service user has not got rights to the database.

  • When setting up a DSN to a SQL server, you must ensure that the "Default Database" setting is set to the database you are connecting to (Windows defaults this to "Master").

 

UserID:  Enter the User ID MARS should use to log on to the database.

 

Password:  Enter the password associated with the above user.

 

Connect:  Click Connect to connect to the Database.

 

Click Next to continue:

 

image1127.jpg

 

 

  • Select the table containing the record you wish to update.

  • Select the Column you wish to update.

  • Enter the new value. *Tip: Use Event, Data, or Dynamic driven data to update multiple records in the table. It is as easy as drag and drop!

  • Click  image16.gif  to add the change.

 

Click Next to continue.

 

image1128.jpg

 

Add some conditions to identify the records to update.

 

Click Next to continue.

 

image1129.jpg

 

 

Review the completed script and make any modifications manually where required.

Click OK to when you are finished.

Insert a Database Record

image1130.jpg

 

DSN Name:  Drop down and select a DSN.  

  • We strongly recommend System DSNs so that the DSN is visible to MARS even when the user associated with the DSN is logged off.  

  • All communication to databases (including Access databases) is done through ODBC, so you must setup a system DSN to the database before you can use this facility.

  • When setting up DSNs we recommend (if possible) that you use Windows Authentication (Trusted Connection).  This ensures that your username and password are not required to be stored in MARS (more secure) and overcomes a large number of security restrictions which Windows places on DSNs.   When using Windows Authentication, make sure that the MARS NT service user (or background application service user) has full rights to the database otherwise, though you will connect when you are logged in, automated scheduling will fail if the Service user has not got rights to the database.

  • When setting up a DSN to a SQL server, you must ensure that the "Default Database" setting is set to the database you are connecting to (Windows defaults this to "Master").

 

UserID:  Enter the User ID MARS should use to log on to the database.

 

Password:  Enter the password associated with the above user.

 

Connect:  Click Connect to connect to the Database.

 

Click Next to continue:

 

image1131.jpg

 

  • Select the table to insert the record.

  • Select a column and provide a value

  • Click image17.gif  to add to the list

  • Insert values for all columns required

 

Click Next to continue.

 

image1132.jpg

 

Review the SQL statement and make modifications manually where required.

 

Click OK when completed.

 

Delete a Database Record

image1133.jpg

 

DSN Name:  Drop down and select a DSN.  

  • We strongly recommend System DSNs so that the DSN is visible to MARS even when the user associated with the DSN is logged off.  

  • All communication to databases (including Access databases) is done through ODBC, so you must setup a system DSN to the database before you can use this facility.

  • When setting up DSNs we recommend (if possible) that you use Windows Authentication (Trusted Connection).  This ensures that your username and password are not required to be stored in MARS (more secure) and overcomes a large number of security restrictions which Windows places on DSNs.   When using Windows Authentication, make sure that the MARS NT service user (or background application service user) has full rights to the database otherwise, though you will connect when you are logged in, automated scheduling will fail if the Service user has not got rights to the database.

  • When setting up a DSN to a SQL server, you must ensure that the "Default Database" setting is set to the database you are connecting to (Windows defaults this to "Master").

 

UserID:  Enter the User ID MARS should use to log on to the database.

 

Password:  Enter the password associated with the above user.

 

Connect:  Click Connect to connect to the Database.

 

Click Next to continue:

 

image1134.jpg

 

  • Select the table

  • Set the selection criteria

 

Click Next to continue

 

image1135.jpg

 

Review the final script and make any changes manually if required.

 

Click OK when completed.

 

Run a Stored Procedure

image1136.jpg

 

DSN Name:  Drop down and select a DSN.  

  • We strongly recommend System DSNs so that the DSN is visible to MARS even when the user associated with the DSN is logged off.  

  • All communication to databases (including Access databases) is done through ODBC, so you must setup a system DSN to the database before you can use this facility.

  • When setting up DSNs we recommend (if possible) that you use Windows Authentication (Trusted Connection).  This ensures that your username and password are not required to be stored in MARS (more secure) and overcomes a large number of security restrictions which Windows places on DSNs.   When using Windows Authentication, make sure that the MARS NT service user (or background application service user) has full rights to the database otherwise, though you will connect when you are logged in, automated scheduling will fail if the Service user has not got rights to the database.

  • When setting up a DSN to a SQL server, you must ensure that the "Default Database" setting is set to the database you are connecting to (Windows defaults this to "Master").

 

UserID:  Enter the User ID MARS should use to log on to the database.

 

Password:  Enter the password associated with the above user.

 

Connect:  Click Connect to connect to the Database.

 

Select a Stored Procedure from the list and enter any required parameters.  For example, if you would normally run your stored procedure by using the query

 

execute myproc para1 para2 para3

 

then

 

Connect to the database and select "myproc" from the list.

Enter in the parameters box:

 

para1 para2 para3

 

 and click OK.

 

Export Data to a Report

image1137.jpg

 

With this task, you can pull data directly from a datasource and format it in Excel or CSV, then deliver the report to a destination. This is all accomplished without the need to write a MS Access report.

 

Select Datasource.

 

image1138.jpg

 

DSN Name:  Drop down and select a DSN.  

  • We strongly recommend System DSNs so that the DSN is visible to MARS even when the user associated with the DSN is logged off.  

  • All communication to databases (including Access databases) is done through ODBC, so you must setup a system DSN to the database before you can use this facility.

  • When setting up DSNs we recommend (if possible) that you use Windows Authentication (Trusted Connection).  This ensures that your username and password are not required to be stored in MARS (more secure) and overcomes a large number of security restrictions which Windows places on DSNs.   When using Windows Authentication, make sure that the MARS NT service user (or background application service user) has full rights to the database otherwise, though you will connect when you are logged in, automated scheduling will fail if the Service user has not got rights to the database.

  • When setting up a DSN to a SQL server, you must ensure that the "Default Database" setting is set to the database you are connecting to (Windows defaults this to "Master").

 

 

Build your query using the Query Tool. Click parse to review the results, then click OK.

 

image1139.jpg

 

Go to the Destination Tab. There you can add a destination for the report. For detailed information on destinations, click here.

 

 

Only three formats are available for this task, CSV, MSExcel 97-2000, and XML.

 

image1140.jpg

 

 

 

Click OK to finish the task.

Table - Create a Table

image1141.jpg

 

DSN Name:  Drop down and select a DSN.  

  • We strongly recommend System DSNs so that the DSN is visible to MARS even when the user associated with the DSN is logged off.  

  • All communication to databases (including Access databases) is done through ODBC, so you must setup a system DSN to the database before you can use this facility.

  • When setting up DSNs we recommend (if possible) that you use Windows Authentication (Trusted Connection).  This ensures that your username and password are not required to be stored in MARS (more secure) and overcomes a large number of security restrictions which Windows places on DSNs.   When using Windows Authentication, make sure that the MARS NT service user (or background application service user) has full rights to the database otherwise, though you will connect when you are logged in, automated scheduling will fail if the Service user has not got rights to the database.

  • When setting up a DSN to a SQL server, you must ensure that the "Default Database" setting is set to the database you are connecting to (Windows defaults this to "Master").

 

UserID:  Enter the User ID MARS should use to log on to the database.

 

Password:  Enter the password associated with the above user.

 

Connect:  Click Connect (Validate) to connect to the Database.

 

Click Next to continue.

 

image1143.jpg

 

Type in a name for the new table, Add the columns, types and sizes to the list and Click Next.

 

image1144.jpg

 

Review the final script and make manual adjustments if required.

 

Click OK when you are done.

Table - Delete a Table

image1145.jpg

 

DSN Name:  Drop down and select a DSN.  

  • We strongly recommend System DSNs so that the DSN is visible to MARS even when the user associated with the DSN is logged off.  

  • All communication to databases (including Access databases) is done through ODBC, so you must setup a system DSN to the database before you can use this facility.

  • When setting up DSNs we recommend (if possible) that you use Windows Authentication (Trusted Connection).  This ensures that your username and password are not required to be stored in MARS (more secure) and overcomes a large number of security restrictions which Windows places on DSNs.   When using Windows Authentication, make sure that the MARS NT service user (or background application service user) has full rights to the database otherwise, though you will connect when you are logged in, automated scheduling will fail if the Service user has not got rights to the database.

  • When setting up a DSN to a SQL server, you must ensure that the "Default Database" setting is set to the database you are connecting to (Windows defaults this to "Master").

 

UserID:  Enter the User ID MARS should use to log on to the database.

 

Password:  Enter the password associated with the above user.

 

Connect:  Click Connect  to connect to the Database.

 

Select the table you wish to delete from the list.  Click OK when you are done.

 

Important! This operation CANNOT be undone. Be sure of your settings before you commit to this task. It is highly recommended that you backup up database as well.

 

 

Table - Add Column to Table

image1146.jpg

 

DSN Name:  Drop down and select a DSN.  

  • We strongly recommend System DSNs so that the DSN is visible to MARS even when the user associated with the DSN is logged off.  

  • All communication to databases (including Access databases) is done through ODBC, so you must setup a system DSN to the database before you can use this facility.

  • When setting up DSNs we recommend (if possible) that you use Windows Authentication (Trusted Connection).  This ensures that your username and password are not required to be stored in MARS (more secure) and overcomes a large number of security restrictions which Windows places on DSNs.   When using Windows Authentication, make sure that the MARS NT service user (or background application service user) has full rights to the database otherwise, though you will connect when you are logged in, automated scheduling will fail if the Service user has not got rights to the database.

  • When setting up a DSN to a SQL server, you must ensure that the "Default Database" setting is set to the database you are connecting to (Windows defaults this to "Master").

 

UserID:  Enter the User ID MARS should use to log on to the database.

 

Password:  Enter the password associated with the above user.

 

Connect:  Click Connect  to connect to the Database.

 

Click Next to continue.

 

image1147.jpg

 

Select the table you wish to modify, Enter the new column name, the type and the size.

 

Click Next to continue.

 

image1148.jpg

 

Review the script and make modifications manually if required.  Click OK when you are done.

 

 

Table - Delete Column from Table

image1149.jpg

 

DSN Name:  Drop down and select a DSN.  

  • We strongly recommend System DSNs so that the DSN is visible to MARS even when the user associated with the DSN is logged off.  

  • All communication to databases (including Access databases) is done through ODBC, so you must setup a system DSN to the database before you can use this facility.

  • When setting up DSNs we recommend (if possible) that you use Windows Authentication (Trusted Connection).  This ensures that your username and password are not required to be stored in MARS (more secure) and overcomes a large number of security restrictions which Windows places on DSNs.   When using Windows Authentication, make sure that the MARS NT service user (or background application service user) has full rights to the database otherwise, though you will connect when you are logged in, automated scheduling will fail if the Service user has not got rights to the database.

  • When setting up a DSN to a SQL server, you must ensure that the "Default Database" setting is set to the database you are connecting to (Windows defaults this to "Master").

 

UserID:  Enter the User ID MARS should use to log on to the database.

 

Password:  Enter the password associated with the above user.

 

Connect:  Click Connect  to connect to the Database.

 

Click Next to continue.

 

image1150.jpg

 

Select the table you wish to modify, then select the column you would like to delete.

 

Click Next to continue

 

image1151.jpg

 

Review the script and make manual changes where required.

 

Click OK when you are done.

 

 

Execute an SSIS Package

With MARS you can execute an SSIS package.

 

image1656.jpg

 

Select Execute an SSIS package from the Database section in the tasks. Drag the task to the right into the task list.

 

Enter the SQL Server name, and your authentication Credentials. From the drop down list Select the desired package.

 

image99.gif

 

Click OK.

 

DTS Server

 

If your SSIS package is located on a DTS Server, enter your server name along with the full path to the SSIS package.

 

Local File

 

If your SSIS package is stored in the local file system, browse to the appropriate package and click OK.

Run Macro or Query

image1737.jpg

 

MARS enables users to run Macros or Queries. Drag Run Macro or Query into the task list.

 

Information Tab

 

Database Location: Browse to the Access database that holds the desired macro or query.

 

Database Password: Enter the password for the database if this is required.

 

WorkGroup Security: If your Access database uses Workgroup Security, then check this option and enter the required credentials.

 

Security File Path: Browse and select the security file (.mdw) which is used by Access to control security for this database. If you don't know what this is, or where it is stored, your System or Database administrator will be able to point you to the right file.

 

Important If you are using Workgroup Security:

  • You must join the workgroup that defines the user accounts used to access the database.

  • Your user account must have Open/Run and Open Exclusive permissions for the database object.

  • Your user account must have Administrator permissions for all the tables, queries, macros, tables and any other objects in the database, or it must be the owner of all tables in the database.

  • Your user account must have Administrator permissions for all objects in the database.

 

image1739.jpg

 

Finally Select the Macro or Query. Click connect.

 

From the drop down menu, select the macro or query you wish to execute.

 

Linked Tables

 

image1740.jpg

 

Login required for linked tables (optional): If the database requires logon credentials, check this option and enter the required username and password. If credentials are not required, uncheck the option and continue as normal (default). All values are optional. You only need to enter a value if your database, security, networking or infrastructure require it. Logins for individual tables can be set as well.

 

Exception Handling

 

image1741.jpg

 

In this tab, you can set how often MARS will retry the Macro or Query if there is a failure of any kind. By default this setting is 3 times.

 

If the task takes longer than a specified time to complete, MARS will treat it as a failed task. Though the time limit is set to 30 minutes by default, this can be adjusted here.

Comments