Alteryx boolean data type issue on latest snowflake driver?

[Niall_R]

Hi all,

One of our Alteryx workflows which pulls data from SQL Server and writes to Snowflake via ODBC has started failing after upgrading to the latest snowflake ODBC driver (snowflake64_odbc-2.19.1.msi). I seem to have narrowed the issue down to, at least, the Boolean data type. The Snowflake table is created successfully, but the insert fails with message like "Output Data (3) DataWrap2ODBC::SendBatch: [Snowflake][Snowflake] (18)   Unsupported data type for bind variable: -7     Insert into... ".

If I use a sample tool to remove all rows (i.e. create table but no insert), it runs without error. Has anyone else experienced this? 

Thanks in advance!

[mceleavey]

Hi @Niall_R ,

I have experienced this before when outputting to an IBM platform specifically. I got around this in a roundabout way by creating a dummy table in the database, writing a pre-create SQL statement to delete * from table, then outputting the records to this table and using a post-create table to copy everything from that dummy table to the existing table (then just to be safe, delete * from dummy table).

This sounds weird but it worked.

Give it a whirl.

M

[Niall_R]

Thanks @mceleavey . I forgot to mention that rolling back the ODBC driver causes this to work again, so that's what we've done in the interim. The step in question is part of a production macro that's we use to transfer dozens of tables from SQL Server to Snowflake, so I'd prefer to minimise disruption and will hold back on the old ODBC version in the hope that a fix comes out (or I found out what I'm doing wrong). Your suggestion is a useful backup option though, in case we're forced to upgrade for other reasons.

[mceleavey]

Yeah, I would definitely recommend rolling it back and getting in touch with the devs to raise it as an issue before committing to the upgrade.

M.

[Niall_R]

For anyone reading this later, I've raised a support ticket to Alteryx for their feedback on this. I've also confirmed that I can reproduce the behaviour with a basic text input tool that passes a single boolean value, so it's nothing to do with SQL Server. After the SF table is created with a single boolean column (no rows) I've also tried every possible permutation of insert, and can't replicate the error. SF accepts insert values of 'TRUE', TRUE, or 1, so seems fairly robust on data type conversion, so I'm guessing it's something with the Alteryx implementation until advised otherwise.

[Niall_R · Accepted solution]

Ok, looks like I was jumping the gun. This driver version isn't supported yet. Response from Alteryx support:

"Driver 2.19.1 is not supported (https://help.alteryx.com/current/DataSources/Snowflake.htm) as it has not been tested yet. I am not aware of of any testing for future versions.
At the moment only ODBC client version 2.13.06.00 is supported."