Correct SnowBL connection string without DSN

[SeanAdams]

Question: Do any of you have a working DSN-free connection string for snowflake Bulk Loader (SnowBL) in an Alteryx Output tool?

Details below

What is DSN-Free?

In order to be able to run our Alteryx canvasses on a multi-node server - we have to avoid using DSNs - so we generally expand connection strings that look like this:

odbc:DSN=DSNSnowFlakeTest;UID=Username;PWD=__EncPwd1__|||NEWTESTDB.PUBLIC.MYTESTTABLE

to instead have the fully described connection string like this:
odbc:DRIVER={SnowflakeDSIIDriver};UID=Username;pwd=__EncPwd1__;authenticator=Snowflake;WAREHOUSE=compute_wh;SERVER=xnb27844.us-east-1.snowflakecomputing.com;SCHEMA=PUBLIC;DATABASE=NewTestDB;Staging=local;Method=user

For Snowflake BL:

Now - for the Snowflake Bulk Loader the same process does not work and Alteryx gives the classic error below

With DSN:

snowbl:DSN=DSNSnowFlakeTest;UID=Username;pwd=__EncPwd1__;Staging=local;Method=user|||NEWTESTDB.PUBLIC.MYTESTTABLE

Without DSN:

snowbl:driver=SnowflakeDSIIDriver;UID=SeanBAdamsJPMC;pwd=__EncPwd1__;SERVER=xnb27844.us-east-1.snowflakecomputing.com;WAREHOUSE=compute_wh;SCHEMA=PUBLIC;DATABASE=NewTestDB;Staging=local;Method=user|||NEWTESTDB.PUBLIC.MYTESTTABLE

Output Data (6) Error SQLDriverConnect: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified" which indicates that the driver details may be wrong

Do any of you have a working fully-loaded snowbl connection string which does not use a DSN, but instead is fully expanded out to the details?

Many thanks

Sean

[apathetichell]

Interesting - do you need to include the Authenticator=Snowflake pair?

[apathetichell]

Played with this a bit - haven't been able to full connect (keeps having issues with my authenticator=externalbrowser) pair - but using  {SnowflakeDSIIDriver} - got me past the driver issue.

also - when writing out - you may need to leave out the DB and Schema since you've set them in the connection string.

[SeanAdams]

Hey @apathetichell - we've confirmed that this is not a missing attribute in the connection string by making sure that we included every single attribute that the DSN is using - what appears to be happening is that under the covers, Alteryx is amending the connection string.

[apathetichell]

And you are using driver={SnowflakeDSIIDriver} - not driver=SnowflakeDSIIDriver? That's when the error message switched for me.

[SeanAdams]

@apathetichell  - do you have a working connection string for snowflake bulk (snowBL: )?   If so - would you mind sharing it (albeit with the sensitive bits masked out) - it would be very helpful to everyone who is trying to use Snowflake Bulk Loader.

NOTE: ODBC is easy to do a DSN-less connection - it's SnowBL that seems to have a problem.

Confirmed: we have tried with both the {} format and the plain format - both give the same error on SnowBL connections

SnowBL:DRIVER={SnowflakeDSIIDriver};UID=username;pwd=__EncPwd1__;authenticator=Snowflake;WAREHOUSE=compute_wh;SERVER=xnb27844.us-east-1.snowflakecomputing.com;SCHEMA=PUBLIC;DATABASE=NewTestDB;Staging=local;Method=user;CLIENT_SESSION_KEEP_ALIVE=FALSE|||NEWTESTDB.PUBLIC.MYTESTTABLE

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

However if you change SnowBL to ODBC it works perfectly - so it is a good connections string, it's just not being accepted by Alteryx.

cc:  @wesley-siu @_PavelP 

[apathetichell · Accepted solution]

let me preface this with - I do not think Alteryx actually supports DSN less bulk for either snowflake or redshift - at least not now. I'd love for someone to come in and say "hey - just declare this value differently" - but I don't think that's going to happen.

Some steps:

1) Swap to the Simba Snowflake ODBC Driver- name that as your driver.

2) Change what you currently have as your table. Your table is listed as DB.SCHEMA.TABLE - it should be just TABLE since you are locking in the schema and the DB in other parts of the string.

3) I don't think methods=user is supported.

Does this work well? Not that i've seen. I had it connecting in once - but now it's back to giving me the s3 error that should be fixed by just declaring the table:

My sample string is:

snowbl:Driver={Simba Snowflake ODBC Driver};UID=MIKEYGG92612;pwd=__EncPwd1__;Authenticator=Snowflake;WAREHOUSE=compute_wh;SERVER=mnb37666.us-west-2.snowflakecomputing.com;Staging=local;DATABASE=ALTERYX_BULK2;Schema=BULK_TEST|||TEST - that makes a connection - writes the fields and then stops. It's not a connection issue - and it's using the driver - and it does create the table - but that's it.

How would I solve this problem? Pending if the need is control of the DSN and expectation of numerous potential DSNs - this is potentially not feasible for security concerns - but have Alteryx write the .reg entries needed for the DSN in workflow. wite/run a .bat file to import into registry. Run  Bulk upload as normal with a DSN...

[SeanAdams]

Hey @apathetichell ,

Appreciate you taking time on this - we've run the option that you suggested and still no joy.

As you say - I'm not sure that Alteryx currently supports DSN-free connections to cloud bulk loads - for example Bulk load operations with DSN-less connection stri... - Alteryx Community

Hopefully this is something that can be added / changed soon.

cc: @_PavelP 

[SeanAdams]

closing this thread - we confirmed with the connectors team that DSN-less connections are not currently supported for snowflake BL.

Adding some folk to the thread to see if there are any updates to be made to the discussion?

@wesley-siu @VojtechT 

[Also_Treyson]

@SeanAdams @apathetichell Did we ever confirm that this is an Alteryx problem or an issue with DSN-Less with snowflake bulk?

Also Sean, good to see you leveraging snowflake!