DSN

Estimated reading: 8 minutes

The DSN (Data Source Name) activity is used to establish a database connection using a pre-configured ODBC (Open Database Connectivity) Data Source Name.

What’s a DSN and why do we need it?

1. A DSN is a configuration setting that defines a connection to a specific database instance.
2. It includes information such as the database server’s address, database name, authentication credentials, and other connection parameters.
3. It acts as an intermediary that allows applications to communicate with databases without needing to know the intricate connection details.

Limitations

1. The DSN activity typically relies on ODBC (Open Database Connectivity) connections, which may limit compatibility with other database connection types.
2. DSN configurations are often static and may not support dynamic changes during runtime, requiring manual adjustments for any modifications.
3. Proper installation and configuration of ODBC drivers are necessary for the DSN activity to function correctly, adding complexity to setup and maintenance.
4. Each DSN activity usually connects to a single data source at a time, which can be restrictive when dealing with multiple databases or data sources in a single automation process.
5. The activity may offer limited configuration options for advanced database interactions, such as custom queries, stored procedures, or specific authentication methods.
a. The custom queries referred as “CREATE, READ, DELETE” SQL commands are restricted and may not be fully supported.
b. Execution on “Stored Procedures” is restricted from this activity.
c. DSN activity supports only “Windows” and “SQL Server” authentication modes, other modes of authentication are limited.
6. The activity is only compatible with the 64-bit version of the ODBC driver.

Properties

CREDENTIALS

Password: This parameter specifies the password associated with the DSN. You need to provide the password that was configured during the creation of the DSN in ODBC. (Refer to the documentation below for more information)

It accepts values in “String” datatype. You can either enter the values hardcoded in “String” format or pass the values as “String” datatype.

Username: This parameter specifies the Username associated with the DSN. You need to provide the username that was configured during the creation of the DSN in ODBC. (Refer to the documentation below for more information)

It accepts values in “String” datatype. You can either enter the values hardcoded in “String” format or pass the values as “String” datatype.

INPUT

CommandTimeout: This parameter indicates the timeout value for executing the activity within the connected database. If the execution cannot be established within this specified time, it will throw an exception.

It accepts values in “Integer” datatype. You can either enter the values hardcoded in “INT32” format or pass the values as “Int32” datatype.

DSNFilepathName: Indicates the file path location of the DSN where the configuration details are stored. It enables the robot to access and utilize the stored connection information for establishing connections with the specified data source.

This is not a mandatory field; you can choose either the “DsnName” or “DsnFilepathName” property.

It accepts values in “String” datatype. You can either enter the values hardcoded in “String” format or pass the values as “String” datatype.

DsnName: Indicates the “Name” of the DSN created while configuration in ODBC.

This is not a mandatory field; you can choose either the “DsnName” or “DsnFilepathName” property.

It accepts values in “String” datatype. You can either enter the values hardcoded in “String” format or pass the values as “String” datatype.

Query*: This parameter indicates to provide the SQL Command/ query that needs to be executed via DSN. You should mention the “Query” based on the “Command Type” chosen in the “QueryType” parameter.

It accepts values in the “String” datatype. You can either enter the values hardcoded in “String” format or pass the values as a “String” datatype.

QueryType: Indicates to choose the “Command Type” that is required to execute along with the provided “SqlQuery”. Select the option from drop-down:

Select: It is used to retrieve data from one or more tables in a database.
Update: It is used to modify existing records in a database table.
Insert: It is used to add new records (rows) into a database table.

By default, the “Select” query is chosen. Refer the tips below in the documentation.

MISC

DisplayName: Displays the name of the activity. The activity name can be customized, which aids in troubleshooting.

SkipOnError: Specify the “Boolean” value as “True” or “False.”

True: Continue executing the workflow regardless of any errors thrown.
False: Halt the workflow if it encounters any errors.
None: If the option is left blank, the activity will, by default, behave as if “False” were chosen.

Version: It indicates the version of the feature being used.

OUTPUT

Datatable: It helps to view the output of the activity in a table view format as the output executed from the provided SQL query. It returns the value in “Datatable” datatype.

Result: It provides the ability to view the execution status of the activity. It returns values in “Boolean.”

True: Indicates that the activity has been executed successfully without any errors.
False: Indicates that the activity has been unsuccessful due to an unexpected error being thrown.

* Represents mandatory fields to execute the workflow.

How to create a DSN file in the ODBC?

ODBC is a standard interface for accessing and working with data from various database management systems (DBMS) using a common method.

1. Interface Standardization: ODBC ensures consistent interaction with databases across different DBMS.
2. Driver-Based Approach: ODBC uses specific drivers for each DBMS, translating commands for smooth communication.
3. Connection Establishment: Configure a DSN for connecting applications to databases via ODBC drivers.
4. Supported Operations: ODBC supports SQL queries, data manipulation, transactions, and stored procedures.

Creating a DSN

To facilitate secure connection with the database and streamline processes, a “DSN” is created. The 64-bit ODBC is accessible to all users through the Windows administrative system. Creating a DSN in ODBC aids users in managing connections with ease and ensuring a secure bridge between the Designer and database server.

Steps to create the DSN:

1. Open the “ODBC Data Source Administrator” dialog by navigating Start -> Control Panel -> Administrative Tools -> Data Sources (ODBC).
2. Select the “System DSN” tab if you want to create the DSN that needs to be accessible for all the users on the system.
a. If you create “DSN” against the “User DSN” tab, it will be accessible only for the user account that created them. Other users cannot access it.
3. Now, click on “Add” option from the right-hand side.
4. A pop-up will appear on the screen, first you need to choose the data source where you want to set up the driver.
a. To automate via SQL server database, you need to choose the “SQL server” option.
b. Now, click on “Finish” button.
5. The next window will prompt you with the configuration steps to create a new data source in the SQL Server.
a. Provide any name with description for the “DSN” to be created.
b. Provide the “SQL Server” name where the DSN needs to be configured; then click on “Next” button.
6. Choose the authentication to verify and login with the provided SQL Server database.
a. Windows NT – If you are authenticating with “Windows” NT credentials, you can opt this option.
b. SQL Server – If you are authenticating with “SQL server” credentials, you can opt this option.
c. If you choose the “SQL Server” option, you need to provide the Server credentials in the next step; then click on “Next” button.
7. Next steps of configuration will be to setup the default file of DSN Configuration. To know about further information, click here.

Here’s an example of how the activity is used in the workflow –

In this example, I’ve already set up the “DSN” in ODBC. Now, I’ll use a “Select” query to retrieve the list of tables available in the DSN.

Please note that I’ve stored the credentials in a corresponding variable, as shown in the example.

Steps to build the bot:

1. Create a new solution.
2. Drag and drop the “DSN” activity into the workflow and set it as the start node.
3. Navigating to the “Credentials” in the properties to provide the database credentials.
a. Here in the properties, (“Password,” and “Username”) i have added the variable respectively.
b. Since my “DSN” is configured in the SQL Server, I have provided the database credentials.
4. Next, navigating to the “DsnName” in the properties to provide the name of the DSN created.
a. Here it is as “Documentation”.
5. Moving to the “QueryType” property, here I am fetching the tables list available in my DSN hence my query is,
a. “SELECT name FROM sys.tables”.
6. Choosing the “QueryType” as “Select” according to my query.
7. Now, declaring a variable in the “Datatable” property to view the output.
a. Click here to refer how to create variables.
8. You can add the “Table Viewer” activity to view the output of the Datatable.
9. Now, save and execute the workflow.

The bot will fetch the tables list available in the DSN in a table format.

DSN

Limitations

Properties

How to create a DSN file in the ODBC?

Creating a DSN

DSN

CONTENTS