How do I set use "Database" Customs Tasks in MARS?

The Database module forms a core part of MARS's data integration capabilities. With the ability to automatically update databases, create tables, and edit records, you can automate thousands of database processes.

Custom Tasks - Database

  • In the Schedule Wizard, go to Custom Tasks.
MS Access: Custom Tasks Wizard in Schedule in MARS.

Database Tasks

MS Access: Database Tasks in MARS.
  • One of the most powerful features in MARS, The Database module forms a core part of MARS's Data integration capabilities.
  • With the ability to automatically update databases, create tables, and edit records, you can automate thousands of database processes.
Tip: Combine the Database Tasks with the Event Based Schedule to create database alerts and data migration processes.
IMPORTANT: These tasks will make modifications to database tables which, in some cases, may be irreversible. Please ensure that you review the resulting scripts and test them thoroughly on a Test system before committing them to your production (live) system.
Make sure you take a full backup of your database before any testing.

Execute SQL Script (from file)

MS Access. Custom Tasks: Execute SQL Script in MARS.

General

  • 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 to save the script.

Options

MS Access. Custom Tasks: Options in MARS.
  • In this section, you have the option to run this task before, after or you can choose both the schedules executes.

Execute an SSIS Package

General

SQL Server

IMPORTANT: MARS requires SQL Server Integration Services Standard or Enterprise X86 (32bit) installed on the MARS machine. ***64bit is not supported***
MS Access. Custom Tasks: Execute an SSIS Package in MARS.
  • Task Name: Give the task a name.
  • SQL Server: Enter the SQL Server credentials.
  • Package Name: Select the package from the drop down list.
  • Click OK to save.

DTS Server

MS Access. Custom Tasks: Execute an SSIS Package in MARS.
  • DTS Server: Enter the DTS Server credentials.
  • Package Path: Write down the package location.
  • Package Name: Select the package from the drop down list.
  • Click OK to save.

Local File

MS Access. Custom Tasks: Execute an SSIS Package in MARS.
  • Package Path: Click (...) to search the package location.
  • Package Name: Select the package from the drop down list.
  • Click OK to save.

Variables

MS Access. Custom Tasks: Execute an SSIS Package in MARS.
  • Add and delete variables for the SSIS package.

Options

MS Access. Custom Tasks: Options in MARS.
  • In this section, you have the option to run this task before, after or you can choose both the schedules executes.

Update a record

General

MS Access. Custom Tasks: Update a record in MARS.
  • 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.
  • Click green arrow to continue.
MS Access. Custom Tasks: Update a record in MARS.
  • Table: Select the table to insert the record.
  • Column Value: Select a column.
  • Value: Provide a value.
  • Click green down arrow to add to the list.
  • Click green right arrow to continue.
MS Access. Custom Tasks: Update a record in MARS.
  • Table: Select the table containing the record you wish to update.
  • Update this column: Select the Column you wish to update.
  • Set its value to: Enter the new value.
Tip: Use Event or Data Driven data to update multiple records in the table. It is as easy as drag and drop!
  • Click green down arrow to add the change.
  • Click green right arrow to continue.
MS Access. Custom Tasks: Update a record in MARS.
  • Review the completed script and make any modifications manually where required.
  • Click OK to save the task.

Options

MS Access. Custom Tasks: Options in MARS.
  • In this section, you have the option to run this task before, after or you can choose both the schedules executes.

Insert a record

General

MS Access. Custom Tasks: Insert a record in MARS.
  • 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.
  • Click green arrow to continue.
MS Access. Custom Tasks: Insert a record in MARS.
  • Table: Select the table to insert the record.
  • Column Value: Select a column.
  • Value: Provide a value.
  • Click green down arrow to add to the list.
  • Click green right arrow to continue.
MS Access. Custom Tasks: Insert a record in MARS.
  • Review the SQL statement and make modifications manually where required.
  • Click OK to save the task.

Options

MS Access. Custom Tasks: Options in MARS.
  • In this section, you have the option to run this task before, after or you can choose both the schedules executes.

Delete a record

General

MS Access. Custom Tasks: Delete a record in MARS.
  • 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.
  • Click green arrow to continue.
MS Access. Custom Tasks: Delete a record in MARS.
  • Table: Select the table to insert the record.
  • Column Value: Select a column.
  • Value: Provide a value.
  • Click green down arrow to add to the list.
  • Click green right arrow to continue.
MS Access. Custom Tasks: Delete a record in MARS.
  • Review the SQL statement and make modifications manually where required.
  • Click OK to save the task.

Options

MS Access. Custom Tasks: Options in MARS.
  • In this section, you have the option to run this task before, after or you can choose both the schedules executes.

Run a stored procedure

General

MS Access. Custom Tasks: Run a stored procedure in MARS.
  • 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.
  • 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
  • Click OK to save.

Options

MS Access. Custom Tasks: Options in MARS.
  • In this section, you have the option to run this task before, after or you can choose both the schedules executes.

Export data to a report

General

With this task, you can pull data directly from a data source and in any output format, then deliver the report to a destination.

Data Source

MS Access. Custom Tasks: Export data to a report in MARS.
  • 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.
MS Access: Get values from database interface in MARS.
  • Build your query using the Query Tool.
  • Parse: Test the Query.
MS Access. Custom Tasks: Export data to a report in MARS.

Destination

MS Access. Custom Tasks: Export data to a report in MARS.
  • Go to the Destination Tab. There you can add a destination for the report.
  • For more information of Destination, click here.
  • Form more information of Output Formats, click here.

Options

MS Access. Custom Tasks: Options in MARS.
  • In this section, you have the option to run this task before, after or you can choose both the schedules executes.

Compact Database

MS Access. Custom Tasks: Compact Database in MARS.
  • With this task, you can compact MS Access Database.
In order for database to be compacted successfully, it must not be in use by any other processes. The compacting process' duration will depend on the size of your database.
  • Task Name: Give the task a name.
  • Database Path: Browse and select the database where the report is located.
  • Database Password (optional): Enter the password to the selected database if necessary.
  • 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.

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.
  • Backup database before compacting to the following directory: Browse and select where the backup database will be located.

Table

Create Table

General

MS Access. Custom Tasks: Create table in MARS.
  • 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.
  • Click green arrow to continue.
MS Access. Custom Tasks: Create table in MARS.
  • Table Name: Give the new table a name.
  • Column Name: Give the column a name.
  • Column Type: What type of data will be written in the column.
  • Column Size: How many character allowed in column.
  • Click down arrow to save the column information. Continue adding as many columns as required.
MS Access. Custom Tasks: Create table in MARS.
  • Review the final script and make manual adjustments if required.
  • Click OK to continue.

Options

MS Access. Custom Tasks: Options in MARS.
  • In this section, you have the option to run this task before, after or you can choose both the schedules executes.

Delete a table

General

MS Access. Custom Tasks: Delete a table in MARS.
  • 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.
  • Table Name: Select the table you wish to delete from the list.
  • Click OK to save.
*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.

Options

MS Access. Custom Tasks: Options in MARS.
  • In this section, you have the option to run this task before, after or you can choose both the schedules executes.

Add column to table

General

MS Access. Custom Tasks: Add column to table in MARS.
  • 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.
  • Click green arrow to continue.
MS Access. Custom Tasks: Add column to table in MARS.
  • Task Name: Give the task a name.
  • Table to Modify: Select the Table that you need to modify.
  • How to modify the Table: Select ADD COLUMN.
  • Column Name: Give the column a name.
  • Type: Select the type of data that will be written in the column.
  • Size: How many characters should be allowed in the column.
  • Click green arrow to continue.
MS Access. Custom Tasks: Add column to table in MARS.
  • Review the script and make modifications manually if required.
  • Click OK to save.

Options

MS Access. Custom Tasks: Options in MARS.
  • In this section, you have the option to run this task before, after or you can choose both the schedules executes.

Delete column from table

General

MS Access. Custom Tasks: Delete column from table in MARS.
  • 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.
  • Click green arrow to continue.
MS Access. Custom Tasks: Delete column from table in MARS.
  • Task Name: Give the task a name.
  • Table to Modify: Select the Table that you need to modify.
  • How to modify the Table: Select DROP COLUMN.
  • Column Name: Select the column that needs to be deleted.
  • Type: This will be greyed out.
  • Size: This will be greyed out.
  • Click green arrow to continue.
MS Access. Custom Tasks: Delete column to table in MARS.
  • Review the script and make modifications manually if required.
  • Click OK to save.

Options

MS Access. Custom Tasks: Options in MARS.

In this section, you have the option to run this task before, after or you can choose both the schedules executes.

Run Macro or Query

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

Information

MS Access. Custom Tasks: Run Macro or query in MARS.
  • 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.
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.
  • Finally Select the Macro or Query. Click Connect.
  • From the drop down menu, select the macro or query you wish to execute.

Linked Tables

MS Access. Custom Tasks: Run Macro or Query in MARS.
  • 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

MS Access. Custom Tasks: Run Macro or Query in MARS.
  • 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.

Options

MS Access. Custom Tasks: Options in MARS.
  • In this section, you have the option to run this task before, after or you can choose both the schedules executes.

Save BLOB to SQL Server

General

MS Access. Custom Tasks: Save BLOB from SQL Server in MARS.
  • Task Name: Give the task a name.
  • SQL-Server Connection properties: Enter the SQL Server credentials.
  • Table: Select the table from the drop down list.
  • Column: Select the column.
  • Value: set the value of the column.
  • Use (+) to add more columns a values as required.
  • Click OK to save.

Options

MS Access. Custom Tasks: Options in MARS.
  • In this section, you have the option to run this task before, after or you can choose both the schedules executes.

Get BLOB from SQL Server

General

Simple

MS Access. Custom Tasks: Get BLOB from SQL Server in MARS.

Advanced

MS Access. Custom Tasks: Get BLOB from SQL Server in MARS.
  • Task Name: Give the task a name.
  • SQL-Server Connection properties: Enter the SQL Server credentials
  • Table: Select the table from the drop down list.
  • Column: Select the column.
  • Records: Define which records are to be downloaded from the table using a SQL Query.
  • Parse: Test the query, ensure the correct data is being shown.
  • File path: Use (…) button to navigate to the folder where the data is to be stored.

Options

MS Access. Custom Tasks: Options in MARS.
  • In this section, you have the option to run this task before, after or you can choose both the schedules executes.