Problem: SQL Server table to CSV Pipeline -mis- translating BIT field to a true/false string value
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
07-01-2022 02:35 PM
Problem: SQL Server table to CSV file Pipeline -mis- translating BIT field to a true/false string value when writing to CSV.
Hi folks,
I’m dealing with a frustrating issue on Snaplogic: I need to go from a Sql server table from a db warehouse to a CSV file for import into a destination system that can only input CSV.
I have a basic 3 snap pipeline that works perfectly for this process, EXCEPT for Snaplogic auto-translating by default the Sql server BIT field to a “true”/”false” string value. This trips up the data import on the destination side.
I know that I can drop in a mapping snap and add a javascript ternary operator ($field == ‘true’ ? 1 : 0) , but then I’d need to map each and every field and pick out the bit fields for that processing. That would be time consuming and inefficient for this bulk data load operation across many tables. I’m trying to avoid that approach but can do if absolutely needed.
Have you encounter this issue of Snaplogic mis-translating the default bit field as true/false strings?
Any hints or assistance greatly appreciated!
According to the Snaplogic docs this is a known issues that was supposedly fix already:
July 2021 Bit is converted to 'true' and 'false'
“Fixed an issue wherein bit data types in the SQL Server - Select table convert to true or false instead of 0 or 1.”
Details/examples of this issue:
The basic Pipeline:
Sql Server table source bit fields:
How the Pipeline writes the bit data field to the CSV file:
Thanks!