Difference between Data Stream-in and Output Data tool when working with DBs

[AT_AYX]

Hello everyone,

I am interested in understanding the difference between Data stream-in and Output tool when working with Databases.

Basically, I want to do an upsert to Snowflake but the fastest way so far that I found was to have an output tool in bulk loading mode with a Pre and Post SQL statement where I truncate a temp a and merge into the target table
However, this method doesn't work when running from Server.

On the other hand, If I use Data stream-in and enforce that the writing should be bulk, this works from the server, but this won't help me as updating inDb in Snowflake is not possible(or at least I didn't find out how yet)

Basically, I want to understand the difference between the two tools and why bulk loading works for data stream-in from Server and doesn't from the output.

Thanks in advance,

[AndrewSu]

@AT_AYX , does the Server machine have the appropriate drivers and permissions to access Snowflake?

see documentation: https://help.alteryx.com/20221/designer/snowflake

According to the documentation, Bulk loading should be supported for the Output Data tool as long as the configurations are correct. 

If this helps resolve your issue, please mark this post as the solution so that other's in the community can benefit from our collaboration. 

Thanks. 

[apathetichell]

TBH - I haven't tried to use bulk uploader for Snowflake on Server with  Output Data - but I agree with @AndrewSu and I do not know why this is not working. What kind of error messages do you get?

[AT_AYX]

Hello @AndrewSu,

So the documentation says that the bulk loading in Snowflake is supported for only DSN-less connection, not DSN.

Many thanks,
Ahmed

[AT_AYX]

Hello @apathetichell,

The problem is that the workflow works in designer but when I tried to run it from Server, it failed saying that DSN or driver is not found.

P.S: the server runs with a service account

[AT_AYX]

Dear all,

The only solution that I have found using the Simba ODBC Driver is to use a DSN-less with a system connection setup on the server itself, that way it will run from server and locally

[AndrewSu · Accepted solution]

@AT_AYX , that's great news!

to reiterate, you said that using the Simba ODBC Driver to use a DSN-less with a system connection setup on the server itself, is the solution you found will work for you to run from server and locally.

Please mark this post as the solution (since you cannot mark your own post as a solution) so that other's in the community can benefit from your findings. 

Thanks.