TECH SUPPORT

Automation Schedule/ Database

Follow

image1122.jpg

 

One of the most powerful features in CRD, The Database module forms a core part of CRD'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.

 

For more information about each of the tasks, click the relevant link below:

 

Execute SQL Script

Update a Database Record

Insert a Database Record

Delete a Database Record

Run a Stored Procedure

Table - Create a Table

Table - Delete a Table

Table - Add a Column to a Table

Table - Delete a Column from a Table

 

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.

 

ChristianSteven Software does not provide support or training for the writing of SQL queries and related databases.  

 

If you are unsure what to do follow the English principle:  "When in doubt, do Not".  Do not attempt to write scripts if you are unsure of what you are doing.  Refer this part of the process to your database administrator or a qualified SQL programmer.

 

Consultancy Services are available as well. One of our Consultants would be happy provide guidance and assistance in working with your systems.

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 CRD 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 CRD (more secure) and overcomes a large number of security restrictions which Windows places on DSNs.   When using Windows Authentication, make sure that the CRD 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 CRD 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 CRD 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 CRD (more secure) and overcomes a large number of security restrictions which Windows places on DSNs.   When using Windows Authentication, make sure that the CRD 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 CRD 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 CRD 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 CRD (more secure) and overcomes a large number of security restrictions which Windows places on DSNs.   When using Windows Authentication, make sure that the CRD 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 CRD 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 CRD 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 CRD (more secure) and overcomes a large number of security restrictions which Windows places on DSNs.   When using Windows Authentication, make sure that the CRD 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 CRD 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 CRD 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 CRD (more secure) and overcomes a large number of security restrictions which Windows places on DSNs.   When using Windows Authentication, make sure that the CRD 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 CRD 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 Crystal 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 CRD 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 CRD (more secure) and overcomes a large number of security restrictions which Windows places on DSNs.   When using Windows Authentication, make sure that the CRD 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 CRD 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 CRD (more secure) and overcomes a large number of security restrictions which Windows places on DSNs.   When using Windows Authentication, make sure that the CRD 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 CRD 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 CRD 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 CRD (more secure) and overcomes a large number of security restrictions which Windows places on DSNs.   When using Windows Authentication, make sure that the CRD 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 CRD 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 CRD 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 CRD (more secure) and overcomes a large number of security restrictions which Windows places on DSNs.   When using Windows Authentication, make sure that the CRD 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 CRD 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 CRD 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 CRD (more secure) and overcomes a large number of security restrictions which Windows places on DSNs.   When using Windows Authentication, make sure that the CRD 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 CRD 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 CRD you can execute an SSIS package.

 

**IMPORTANT**

 

-CRD requires SQL Server Integration Services Standard or Enterprise X86 (32bit) installed on the CRD machine. 64bit is not supported.

 

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

 

image98.gif

 

Click OK.

 

 

 

 

Comments