Hello,
I am trying to load data from SQL Server table that has a column with varbinary(MAX) COL1 (sample data 0x2E6A70672C2E7064662C2E7478742C2E646F632C2E646F63782C2E7274662C2E706E670D0A0D0A0D0A46696C65466F726D61746573) to Snowflake using ADF pipelines. I have created a simple pipeline to using Copy activity to read from the source table and stage the data to the azure blob(using enable staging option) and then load to Sink(snowflake). The process errors out while loading the data with below error.
ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [22000] The following string is not a legal hex-encoded value: '0x255044462D312E360D25E2E3CFD30D0A33372030206F626A203C3C2F4C696E656172697A656420312F4C2032303539372F4' File 'a264d570-05de-4ef0-9028-9034ed0c360c/SnowflakeImportCopyCommand/data_a264d570-05de-4ef0-9028-9034ed0c360c_164ddc6c-8166-4d3e-aa2e-72c57fd6565b.txt', line 2, character 27 Row 1, column
I can cast data while reading to TEXT however since adf generated COPY statement in the backend, I am not able to convert TEXT back to BASE64 to load to Snowflake.
Any help is appreciated! Also if there is a similar question already answered then please point me to it because I was not able to find the link.