Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Bulk load operations with DSN-less connection string

martin_bits2b
5 - Atom

Hello,
please can I ask You for ideas on how to solve this situation:
We have an enterprise environment with the Alteryx Server and internal community of Developers. Because of the corporate rules and capacity of the team responsible for the Alteryx platform, we are not able to maintain DSN connections on the server for each workflow published on the TEST or PROD environment. Because of this, We stick with DNS-less connections format for most of our technologies (Teradata, Oracle, SAP HANA, AWS Redshift - are the primary data sources).
The problem begins when we want to use Bulk loading operations together with DSN-less connections string.
It seems Alteryx does not support the definition of "Bulk Connection String" without DSN name in it and always reports some error of non-existing DSN or driver.
Example of working DSN-less string for AWS Redshift write:

 

odbc:DRIVER={Amazon Redshift (x64)};Server=<HOST>;Port=5439;UID=<USERNAME>;Password=<PASSWORD>;Database=<DATABASE_NAME>|||<TABLE_NAME>

 

 

Example of the same string with rsbl prefix and additional needed parameters (returning error: Error SQLDriverConnect: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified):

 

rsbl:DRIVER={Amazon Redshift (x64)};Server=<HOST>;Port=5439;UID=<USERNAME>;Password=<PASSWORD>;Database=<DATABASE_NAME>;Bucket=<STAGING_S3_BUCKET_NAME>;Access=<AWS_ACCESS_KEY>;Secret=<AWS_SECRET_KEY>;URL=Default|||<TABLE_NAME>

 

 

We tested various approaches like creating a "dummy" DSN name, call this DSN in connections string and change values on the fly, but with no luck. AWS Redshift is crucial for this bulk load option because load through standard ODBC is slow and not recommended from AWS (https://docs.aws.amazon.com/redshift/latest/dg/t_Loading_data.html).


Please, do you have some advice on this? How do you solve these issues in environments where it is not possible (or not effective) to maintain DSN definition directly on the Alteryx Server host system level.
Thanks!

 

10 REPLIES 10
francis_oy
8 - Asteroid

I am trying to the same thing here. I am trying to let my workflow independent with any gallery connections. So I am always using DSN-less connection string for all input and output tool. When I was trying to do the redshift bulk load, I found exact the same error as you do. I am hoping we can get this resolved by Alteryx.

TessaS
Alteryx
Alteryx

Hi @martin_bits2b ,

 

Errors saying 'non-existing DSN or driver' indicate that the driver isn't installed on the server. Can you confirm that the driver installed on the server is called this exactly? 

Amazon Redshift (x64)

 

The name of your driver will need to match the above character for character. If the driver installed on the machine is named something like Amazon Redshift or Amazon Redshift (x64) v 1.0.4, it won't be able to find the driver it's looking for.

 

As a test, you may want to try using that same DSN-less connection string (which looks correct btw) from the Designer on the server machine itself. If you can get that to work, you should be able to get the connection to work in the Gallery, as well.

 

Thanks!

Tessa Smithers

Sr. Technical Account Manager
Alteryx, Inc.


francis_oy
8 - Asteroid
Hi TessaS,

I have tested the DNS-less redshift ODBC connection (non rsbl) in our company's gallery and it succeeded.

The ODBC connection string I am using is as below:


* Non-Redshift Bulk Loader - this is working both on my local machine an gallery:
odbc: Driver={Amazon Redshift (x64)};Server=[Server Name];Database=[DB name];UID=[UID];PWD=[PWD];ClusterId=[ClusterId];Region=US-EAST-1;DbUser=[DB user];DbGroups=[DB Group];IAM=1;plugin_name=Ping;idp_host=[prodfederate host];idp_port=443;preferred_role=[preferred role];partner_spid=amazonRedshift


* Redshift Bulk Loader - this is not working either on my local machine or gallery:
rsbl: odbc: Driver={Amazon Redshift (x64)};Server=[Server Name];Database=[DB name];UID=[UID];PWD=[PWD];ClusterId=[ClusterId];Region=US-EAST-1;DbUser=[DB user];DbGroups=[DB Group];IAM=1;plugin_name=Ping;idp_host=[prodfederate host];idp_port=443;preferred_role=[preferred role];partner_spid=amazonRedshift;Bucket=[bucket path];Access=[access key];Secret=[secret key];URL=Default;Signing=V4

So I don't believe this is a driver issue. It seems if we use rsbl, we have to use DSN instead of DSN-Less connection style.

Thanks
Francis
amti
7 - Meteor

Hi @TessaS , @francis_oy 

 

I'm also having this exact same error but for Snowflake Bulkload with DSN-less connection string.

Error: Output Data (7): Error SQLDriverConnect: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

 

snowbl:Driver={Simba Snowflake ODBC Driver};Server=SNOWFLAKE_SERVER;Database=XXX;Schema=YYYY;Role=X1;Warehouse=X2;proxy=XXX:8080;uid=MYUSER;pwd=__EncPwd1__;
 
The driver is already used with that name in other components that we've tried with DSN, so it doesn't really seem a problem with the driver. Anything between 32 and 64 bit driver? Proxy was also used before with DSN, so no issue there ... is really hard to get bulkload to use with connections from the Gallery.
I already packed my workflow with a check in "Manage assets"->"Group by asset tools".
francis_oy
8 - Asteroid

@amti ,

 

Have you tested your DNS less string in your input tool (without bulk load parameters)? I am not familiar with Snowflake, but for Redshift ODBC driver, it looks like below:


odbc: Driver={Amazon Redshift (x64)};Server=[Server Name];Database=[DB name];UID=[UID];PWD=[PWD];ClusterId=[ClusterId];Region=US-EAST-1;DbUser=[DB user];DbGroups=[DB Group];IAM=1;plugin_name=Ping;idp_host=[prodfederate host];idp_port=443;preferred_role=[preferred role];partner_spid=amazonRedshift

If the input connection is a success, then it means the issue is Alteryx is not able to handle DSN-less bulk load connection string?

 

Regards

Francis

spuvvada
5 - Atom

Hi Fracis, 

I'm also facing exact same issue, are you able to solve this? If not, did you use any other workaround? 

 

Thanks

deeptigupta
5 - Atom

Hi

I'm also facing exact same issue, where DSN Connection string fails on gallery and redshift bulk loader needs DSN connection string.

are you able to solve this? If not, did you use any other workaround?

Mahadeva
8 - Asteroid

Hi All, I'm also having the same requirement to use DSN less bulk load for AWS Redshift DB.

Request support and guidance if anybody has found a solution to perform the same.

 

Thanks in advance.

Nutan
5 - Atom

Hi, I am looking for a dsnless connection string for postgres bulk connection, request you to help with that as well.

Labels