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!
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.
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!
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
@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
Hi Fracis,
I'm also facing exact same issue, are you able to solve this? If not, did you use any other workaround?
Thanks
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?
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.
Hi, I am looking for a dsnless connection string for postgres bulk connection, request you to help with that as well.