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]

@Also_Treyson I do not believe that the Bulk Snowflake connection is a thing outside of Alteryx (ie it's an Alteryx specific invocation of the ODBC driver) - so my hypothesis is that this is an Alteryx thing.

[shawnmacnova21]

@SeanAdams is there an Idea submitted for Alteryx to get this working? Would love to give that some likes to get the ball rolling!

We dont setup DSN for any connections on the Server - is there any documentation for that?

[SeanAdams]

Hey @Also_Treyson  - we did confirm that Alteryx is injecting information into the connection string which breaks this - and I believe that the connectors team are working on a fix.

[SeanAdams]

Hey @shawnmacnova21  - there are a few ideas on this that you may be interested in adding your thoughts to ( and thank you for offering):

DSN-Less connection for Snowflake Bulk - Alteryx Community

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Ideas/Update-help-to-reflect-DSN-free-conn...

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Ideas/Convert-from-DSN-to-DSN-Less/idi-p/1...

Hopefully there are a few there that you find common cause with?

[naveenu]

From Patch 2022.1.1.42707 onwards the Snowflake SNOWBL driver is working and tested successfully.

Below the the sample DSNLESS connection string:

SNOWBL:DRIVER={SnowflakeDSIIDriver};SERVER={server};DATABASE={db};SCHEMA={schema};ROLE={role};WAREHOUSE={warehouse};AUTHENTICATOR={oauth};UID={uid};token=__EncPwd1__;PROXY={proxy};Staging=local;Method=user|||<table>

[lzanotti]

hi @naveenu  we are doing exactly the same but encountering this error sadly:
Error: Output Data (4): The COPY failed with error: We were unable to remove some files from S3 after the copy operation!10Error from AWS: The specified bucket does not exist
Our connection string though looks like this:
SNOWBL:DRIVER={SnowflakeDSIIDriver};SERVER={XXXXX};UID={SVC_ALTERYXSPODEV};ROLE={ANALYTICS_SS};AUTHENTICATOR={snowflake};PWD=XXXXXX;

We are specifying the database and schema in the Output tool directly.
The good news is that the output tool is loading data very quickly, almost at the same speed as the input.
But then it throws that error.
Do you know what we are missing please?
Thanks in advance
Luca

[apathetichell]

We were unable to remove some files from S3 after the copy operation!10Error from AWS: The specified bucket does not exist is either an AWS error - your bucket is wrong. or a permission issue - you can copy the file to the bucket. you cannot delete the file from the bucket. check your IAM policy and bucket names.

[markboardman]

To confirm also as I work with Luca, we are on version Version: 2022.1.1.30961 is this a known issue on this version?

[apathetichell]

@markboardman--- if you read through this thread --- DSNless bulk loading for Snowflake was not working at all. The fix was deployed about a year ago for version 2022.1.1.4X. 2022.1.1.3X will not work. You will need to upgrade. I stopped following this after some initial tests/exploring so I've never seen the successful implementation in any version - but I'd strongly recommend applying the 2022.1.1.4X patch if this is something your team needs.

Having said that - your issues with an S3 bucket not deleting does not sound like an Alteryx issue - and instead sounds like an AWS bucket policy/IAM policy issue. If that's your hold up - I'd look at the policy of your IAM role/user to see what access they have to the bucket.

[naveenu]

You will have to play for with these parameters:
Staging=local;Method=user --> I used ODBC, so this worked.

For S3 Bucket try playing with these:
Staging=Amazon S3;AccessKey=xxxx;Server-Side Encryption=None

If you want more details about the parameters, drop output tool on canvas and choose Snowflake-->Bulk driver 😎