Bulk connection without ODBC

Question

I'm newly educated Data engineer, and thus new to Alteryx and some of the industry database connections/solutions.

Several days ago I received a task from my supervisor, and I have been research it since.

I cannot find anyone else that had to face the same problem despite the fact that my scenario doesn't seems rare to me.

Long story short:

I am managing alteryx server at my company.

In this company, often users need to upload or download large sets of data.

Those extractions could takes hours, hold and demand costly resources.

So I was asked to look into the option of integrating bulk connection.

the problem is that in our company there are more than 40 Alteryx users and hundreds if not thousands of databases.

It makes no scene to create a ODBC(or OleDB) connection in the traditional way -> Directly for each user, to each database.
It is not manageable, or rational.

Is anyone know or tried a different way to enable bulk connection without configuring specific connection one at the time? More generic approach to this?

Thanks for the help

Mahadeva · Answer

@Beau_Hunt I'm faced with the same requirement of DSN less Amazon Redshift bulk load for output tables.

Would like to know if you have found any solution.

Beau_Hunt · Answer

Hello,

I know this thread is old. Is there a way to do the Redshift Bulk upload without having to use a DSN? We've been able to modify connection strings for other types of Alteryx connectors to eliminate the DSN.

I did check the connection strings site you referenced but did not see any entries for Redshift.

Thank you.

SophiaF · Answer

@Sall many databases support a 'DSN-less' connection, meaning that you use a connection string with the different parameters listed within it, rather than a Data Source Name you set up in ODBC admin. Here's an example with SqlServer:

DSN connection via ODBC admin:

odbc:DSN=MySqlConnection

DSN-less connection string:

odbc:Driver={SQL Server Native Client 11.0};Server=myServerAddress;
Database=myDataBase;Trusted_Connection=yes;

I often use this website as a good starting point for finding the parameters for each driver: https://www.connectionstrings.com/. I would also suggest looking into shared Gallery Data Connections where you can centrally manage all of these connections (if you are using Alteryx Server). Once you create your connection strings, you can create a Gallery Data Connection (using the option 'Other') and give permissions to users to access these connections in the Designer. Please see this link for more info.