How to access ODBC DSN in BI / Reporting / ETL Apps / Programming Languages

[daniel]

In this article, we will show you how to access ODBC DSN in differents tools.

Previously you needed to create an ODBC driver (JSON driver, XML driver, Salesforce driver, etc)

Read data in Excel from the DSN

1. In Excel click Data, then select Get Data, proceed with From Other Sources and choose From ODBC item. This will get data from ODBC data source we created:
   

   

2. A small window opens, then simply select the data source you created before:
   

   

3. Most likely, you will be asked to authenticate to a newly created DSN. Just select Windows authentication option together with Use my current credentials option:
   

   

4. Finally, you will be asked to select a table or view to get data from. Select one and load the data!
   

   

5. Finally, use data extracted from XML API in an Excel worksheet:
   

   

Read data in Power BI from ODBC

1. Once you open Power BI Desktop click Get Data to get data from ODBC:
   

   

2. A window opens, and then search for “odbc” to get data from ODBC data source:
   

3. A small window opens, then simply select the data source you created before:
   

   

4. Most likely, you will be asked to authenticate to a newly created DSN. Just select Windows authentication option together with Use my current credentials option:
   

   

5. Finally, you will be asked to select a table or view to get data from. Select one and load the data!
   

   

6. Finally, use data extracted from XML API in Power BI reports:
   

   

Create Connection in Informatica Workflow Designer

1. Open Workflow designer [W] icon

2. Goto Connections > Relational
   

3. Click New and select ODBC
   

4. Now on the ODBC connection setup enter connection name, some fake userid / password (this is a required field but its ignored by JSON Driver)

5. In the Connection String field enter the exact same name of DSN (Open ODBC Data Sources UI to confirm)
   

6. Click OK to close the connection properties