Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.

How To: Connect to an OleDB Data Source

MichaelAd
Alteryx
Alteryx
Created

How To: Connect to an OleDB Data Source

 

Alteryx can connect to databases using different methods. This guide will focus on setting up an OleDB connection to SQL server, however, the same general process can be followed to connect to any database via OleDB.

 

Prerequisites

 

  • Product - Alteryx Designer or Server.
  • OleDB driver for the database (if it is not currently present on your machine, you must download and install it prior to using this guide. The download links to many of the drivers can be foundhere. If you are unsure of where to get the driver or which driver to use please contact your IT or database admin for this information).

     

 

Procedure

 

  • Open Alteryx Designer and drag and drop the “Input Data” tool onto the canvas

ole1.png

 

 

Before 2019.2

  • Click the drop-down to "Connect to a File or Database." Then select "Database Connection" > "New OleDB Connection…" The "Data Link Properties" window should appear.

ole2.png

 

 

2019.2 and later

  • Click OleDB under Microsoft SQL Server.The "Data Link Properties" window should appear.

2019-07-25_14-53-20.jpg

 

All versions

 

  • Select the driver for the database you wish to connect to and click “Next.” In this guide we will be selecting the “SQL Server Native Client 11.0” driver. This is the recommended driver for Microsoft SQL Server.

 

2019-07-25_14-18-38.jpg

 

  • On the next screen, enter the server name by either selecting it from the drop-down, or if it does not appear, by typing it in manually
  • Select the authentication method of the database. If your database uses “Windows Authentication” additional login information is not required. If your database uses user ID/PW authentication please enter the username and password you will be connecting with. If you are unsure of your login information please contact your IT or database admin.
  • Select the database on the server you wish to connect to by selecting it in the drop-down.
  • Click the “Test Connection” button to test that you are set up correctly.

ole4.png

 

  • Upon successful test you will get the below popup window:
  • Click the “OK” button
  • On the “Data Link Properties” window click “OK”

ole5.png

 

  • The “Choose Table…” Window should appear.
  • In the window select the table you wish to connect to.

ole6.png

 

  • After successfully connecting to a table, the connection will now be saved in the “Connect a File or Database” drop-down, both under the initial drop-down and also under “Database Connection”

ole7.png

 

 

Additional Resources

 

Comments
jineshnp31
8 - Asteroid

Hi @MichaelAd,

 

Thank you for sharing this article.

 

I am trying to set up the OLEDB with INDB connection and want to save it to a file for the newest driver MSOLEDBSQL. I received the given error. Looks like this is not supported with Alteryx. Kindly let me know about your thoughts on this one or if there are other ways around this? 

 

jineshnp31_0-1589755377677.png

 

Driver Details: https://docs.microsoft.com/en-us/sql/connect/oledb/oledb-driver-for-sql-server?view=sql-server-ver15

 

Thank you! 

melroseboo
6 - Meteoroid

I am having the same issue as the above with connecting using the new provider MSOLEDBSQL19, with the in-DB input tool. Any solve?