Handling Binary Data Transmission to Snowflake – SQL Compilation Error Analysis

Issue Summary:

We encountered a SQL compilation error while trying to insert binary data into a Snowflake table using a parameterized query.

Error in SQL Query : insert into "TGT_REPORTS"."ATTACHMENTS" ( ATTACHMENT,MODIFIEDDATE_STR,CREATEDDATE,CREATEDBY,FILENAME,EMPLOYERID,TLND_IS_DELETED,TLND_DATA_LOAD_ID,MODIFIEDBY,DOCUMENTACTIVE,ID,ATTACHUID,CREATEDDATE_STR,TLND_LOAD_DATE,MODIFIEDDATE,UPSERT_ID ) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
SQL compilation error: error line 1 at position 258
Bind variable ? not set.

The requirement involves moving Excel, PDF, CSV, and text files stored in a SQL Server database to Snowflake.

Issue Cause:

We found that sending binary data to a Snowflake database table is currently not supported natively in the product. Upon further investigation, particularly for PDFs and Excel files, we found a limitation in our runtime framework. 

When we attempt to read the binary data (bytes) for PDF or Excel files from SQL Server, the data becomes corrupted. This is due to our framework’s mechanism of processing data through XML, where it fetches data and stringifies it as XML field content. Unfortunately, during this stringification process, binary bytes are altered, leading to data corruption.

We further analyzed how to read the Excel and PDF bytes without causing corruption. This can be achieved through a custom implementation at the source as well. Therefore, the conclusion is that we need to implement custom handling at both ends—i.e., at the Database Driver level for both the source (SQL Server) and the target (Snowflake).

Suggested Solution:

To resolve the issue, a custom implementation is needed at both the source (SQL Server) and target (Snowflake) levels.

We've attached the wrapper drivers for both SQL Server and Snowflake that will allow us to read byte data from SQL Server and write it to the Snowflake database without any data loss.

Please follow below steps:

1. Create a Database Driver activity: 

   1. Source (MS-SQL):
      1. Upload the SQLDriverWrapper.jar along with the original MS-SQL JDBC jar
      2. Update the driver class name to SQLDriverWrapper
      3. Save it
   2. Target (Snowflake):
      1. Upload the MySnowflakeDriver.jar along with the original Snowflake JAR
      2. Update the driver class name to net.snowflake.client.jdbc.MySnowflakeDriver
      3. Save it
2. Create New Database Info Activities:
   1. Create a Database Info activity for each (Do not modify the existing ones).
   2. Use it in the Source and target layouts, and other activities as required.
3. Execute the transaction:
   1. The binary attachment should be transferred to the Snowflake table.

Attached the wrapper driver jars

MS-SQL: SQLDriverWrapper.jar

Snowflake: MySnowflakeDriver.jar