Database - Open SQL Connection

Declaration

<AMDATABASE SESSION="text" CONNECTIONTYPE="text (options)" 
CONNECTION="text" TIMEOUT="number" MEASURE="text (options)" LOGIN="YES/NO" />

Description: Opens a database connection using a predefined connection string a data link file or a custom connection string. The connection is identified by a unique session name, which can be referenced by subsequent Database steps. This allows a single task the ability to perform multiple database operations concurrently.

Practical Usage

Used to establish a persistent connection for use with other Database steps. This activity also provides the option to create and name a new pre-defined connection string that can be used in subsequent steps and tasks. Use the Close SQL Connection activity to close a SQL connection that is currently open.

General Parameters

Property

Type

Required

Default

Markup

Description

Type

Text (options)

Yes

 

  1. CONNECTIONTYPE="database"

  2. CONNECTIONTYPE="connection_string"

  3. CONNECTIONTYPE="file"

  4. CONNECTIONTYPE="predefined"

Specifies how this activity should initially connect to the database server. The available options are:

  • Database (default) - Database connection will derive from a new OLEDB connection string created via the Data Link Properties Wizard.

  • Connection string - Database connection will be established using a specific connection string. Enables you to copy an existing string onto the properties of this activity.

  • Data link file - Connection will be established via the data link file specified.

  • Predefined - Connection will be established via a pre-defined connection string.

Session name

Text

Yes

SQLSession1

SESSION="SQLSession2"

The name of the session to create. Other database activities can be linked to this session, eliminating redundancy. Numerous sessions can exist within a single task providing efficiency. The default value for this parameter is DatabaseSession1. Use the Close SQL Connection activity to close a SQL connection and end the session.

Database connection

Text

Yes if connection type set to database

(Empty)

CONNECTIONSTRING=

"AM1P2qCkbYiFHdqWN7qDphOEw==aME"

Specifies a custom OLEDB connection string. The connection string provides driver, server, username, password and other important information to the OLEDB subsystem so that the connection can be made. To generate a connection string, press the Build connection button. This opens the Data Link Properties Wizard dialog which will help you with construct of your connection string. To clear an existing connection string, press the Clear button. This parameter is available only if the Type parameter is set to Database.

Connection string

Text

Yes if connection type set to connection string

(Empty)

PREDEFINEDCONNECTION=

"%MyPreDefinedConnection%"

Specifies the OLEDB connection string. Use this option to copy an existing connection string and paste it in the provided text box. This parameter is available only if the Type parameter is set to Connection string.

Data link file

Text

Yes if connection type set to data link file

(Empty)

CONNECTION="c:\temp\fileName.udl"

The path and filename of the data link (.udl,.dsn) file to connect with. Click the folder icon to open a standard Explorer dialog allowing for easy navigation to the desired data link file. This parameter is active only if the Type parameter is set to Data link file.

Predefined connection string

Text

Yes if connection type set to predefined

(Empty)

CONNECTION="/"

The newly built connection string to be used as proper connection to the database.

Prompt user for a name and password

Yes/No

No

No

LOGIN="YES"

If set to YES, specifies that the user should be prompted to enter a username and password for the database server each time the task is run.Set to NO by default.

Protect connection string

 

 

 

 

If enabled, the value entered in the Connection string parameter will be encrypted in both visual and AML mode as a means of protecting sensitive data that may be contained in the connection string. If disabled (default), the connection string will appear as normal readable text. Enable this option if your connection string contains sensitive data that you may not want others to view. This is a visual mode parameter used only during design time, therefore, contains no properties or markup. It is available only if the Type parameter is set to Connection string.

Advanced Properties

Property

Type

Required

Default

Markup

Description

The connection times out after

Number

No

(empty)

TIMEOUT="25"

Specifies how this activity should initially connect to the database server. The available options are:

  • Database (default) - Database connection will be defined via Data Link Properties, the standard Windows system interface for configuring connection strings to data sources.  

  • Connection string - Connection will be established using a specific connection string. Enables you to copy an existing string onto the properties of this activity.

  • Data link file - Connection will be established via the data kink file specified.

  • Predefined - Connection will be established via a pre-defined connection string.

Measure

Text (options)

No

Seconds

  1. Measure="milliseconds"

  2. Masure="seconds"

  3. Measure="minutes"

  4. Measure="hours"

The time measurement to associate with the numeric value above. The available options are:

  • Milliseconds - The value will be measured in milliseconds.

  • Seconds - The value will be measured in seconds.

  • Minutes - The value will be measured in minutes.

  • Hours - The value will be measured in hours.

Description tab - A custom description can be provided on the Description tab to convey additional information or share special notes about a task step.

Error Causes tab - Specify how this step should behave upon the occurrence of an error. (Refer to Task Builder > Error Causes Tab for details.)

On Error tab - Specify what AWE should do if this step encounters an error as defined on the Error Causes tab. (Refer to Task Builder > On Error Tab for details.)

Example

Description: Establish SQL connection using predefined connection string named "%thecommection%".  SQL session name is "SQLSession10".  

<AMSQLOPENCONNECTION SESSION="SQLSession10" 
PREDEFINEDCONNECTION="%thecommection%" />