Database - Open SQL Connection

Declaration

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

Related Topics    

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.

Parameters

General

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 by way of 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 by way of the data link file specified.
  • Predefined - Connection will be established by way of 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 file name 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 selected, specifies that the user should be prompted to enter a username and password for the database server each time the task is run. Disabled 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

Property Type Required Default Markup Description
The connection times out after Number No (Empty) TIMEOUT="25" A specified amount of time that will be allowed to elapse before a timeout event occurs. Enter a numeric value along with a time measurement (described in the Measure parameter below).
Measure Text (options) No Seconds
  • MEASURE="milliseconds
  • MEASURE="seconds
  • MEASURE="minute"
  • 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

Error Causes

On Error

Example

NOTE:
  • The sample AML code below can be copied and pasted directly into the Steps Panel of the Task Builder.
  • Parameters containing user credentials, files, file paths, and/or other information specific to the task must be customized before the sample code can run successfully.

Description

This sample task establishes a SQL connection using a predefined connection string.

Copy 
<AMDATABASE SESSION="SQLSession10" CONNECTIONTYPE="predefined" CONNECTION="%theconnection%" />