Using ODBC Connection strings in ETL Tools

What is ODBC?

ODBC stands for Open Data Base Connectivity, a standard database access method developed by Microsoft Corporation.

It makes it possible to access any data from any application, regardless of which database management system (DBMS) is handling the data. ODBC manages this by inserting a middle layer, called a database driver, between an application and the DBMS.

The purpose of this layer is to translate the application's data queries into commands that the DBMS understands. For this to work, both the application and the DBMS must be ODBC-compliant -- that is, the application must be capable of issuing ODBC commands and the DBMS must be capable of responding to them.

What is a DSN?

A DSN (Data Source Name) is an identifier which defines a data source for an ODBC driver.

For example DSN for MS SQL Server would consists of:

• Database name
• Server name
• Database driver
• User Name
• Password

The Problem

This information is stored in windows registry and this fact introduces additional work for system administrators. Imagine following situation you have CRM application and you use ODBC DSN to connect to it. It means that on every PC the Admistrator must create ODBC DSN manually. The problem becomes worse if you have a lot of different databases and large amount of users.

The Solution

One of the benefits of using DB Software Laboratory ETL tools is the support for ODBC connection strings.

Using ODBC connection string gives you the ability to connect to the database without creating an ODBC DSN.
 
For example to connect to MS SQL Server 2008 you may use the following connection string.
 
Driver={SQL Server Native Client 10.0};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

You may find more information about connection strings at http://www.connectionstrings.com

Related Products

Visual Importer loads data into ANY database from Text, Excel, MS Access, DBF files or any ODBC compliant database.  It is easy to use and was designed especially for non technical users.

Visual Importer Professional is an automation tool. User can design Import, Export and SQL scripts, add them to the Package and schedule it for execution on regular basis.

Visual Importer Enterprise is an ETL tool designed to automate database loading process. Typical usage of it would be download files from FTP Server, load them into the database, run stored procedure or Sql script and send email to adminstrator once package is completed.

Visual Importer Enterprise includes execution agent which can be run as a Windows service.

Advanced ETL Processor is designed to automate extracting data from ANY database, transform, validate it and load into ANY database . Typical usage of it would be extract data from Excel File,Validate Date Formats, Sort data, deduplicate it and load it into Oracle database, run stored procedure or Sql script, once loading is completed. Unlike Oracle SQL loader, BCP,  DTS or SSIS Advanced ETL Processor can also add new and update old records based on primary key