bubba
New Contributor
Joined 5 years ago
User Profile
User Widgets
Contributions
Truncation error with MS SQL Bulk Load snap, column reported not issue, which column is real issue?
We're occasionally getting a frustrating truncation error using MS SQL Bulk Load snap. The error 'column' reported is -not- the issue, so which column is causing issue?? Error details below. I can isolate the incoming record from the incoming csv file (Row 74740 in this case) , the record/doc has many fields and the reported column (Column 13 in this case) is -NOT- where the data truncation error is happening. Is there a way I can pin point the actually field/column which the trunc error is happening? I suspect the fields are being re-ordered in some manner to feed to the bcp command for the snap. Is there a way to see the -actual- bcp cmd text the snap is passing to bcp for execution? The 'Column 13' reported in this error is not helpful in debugging the pipeline when it fails with these data truncation errors. MUCH THANKS for any assistance or insights! Error below: Failed loading record Resolution: Please address the reported issue. Reason: #@ Row 74740, Column 13: String data, right truncation @# wProblem: SQL Server table to CSV Pipeline -mis- translating BIT field to a true/false string value
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' May 2019 https://docs-snaplogic.atlassian.net/wiki/spaces/SD/pages/797704281/May+2019+4.17+Release+Notes?showComments=true&showCommentArea=true “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!Questions on using the SQL Server – Merge snap, where is WHEN MATCHED logic?
Hello, I’m a new user to Snaplogic, so go easy. 🙂 My question is on using the “SQL Server – Merge” snap: I’ve created a pipeline to import from a sftp csv-formatted source to merge (update or insert) into a table on an SQL server database. It’s partially working! When I run the pipeline it successfully goes through the sftp->CSV parser->Mapper->SQL Server – Merge snaps to insert the csv-mapped records into the MS-SQL target db table with no problem. But when running a second time I get a “Violation of PRIMARY KEY constrain” error, so new records can be inserted, but not updated. My questions are: When looking at the Merge snap settings I see no place to put the WHEN MATCHED or WHEN NOT MATCHED BY logic? How is the Identity Column setting used? I though it might be a setting for the primary unique key, but that does not seem to be the case. With other pipelines I was able to setup a route-error-data view to capture errors into a file. When I attempt that with the SQL Server Merge snap I get an ‘Error branch will overlap with existing Snaps” error. I’ve attempted to rearrange the pipeline snaps but to no avail, I still cannot setup the error route view. I’ve also reviewed and read the snap documentation at: https://docs-snaplogic.atlassian.net/wiki/spaces/SD/pages/1439070/SQL+Server+-+Merge I see that there’s a “ELT Merge Into” snap, but I do not have access to that snap from the designer tool. https://docs-snaplogic.atlassian.net/wiki/spaces/SD/pages/1123745847/ELT+Merge+Into Should I be using that snap instead? Thanks for any insights or guidance!
Groups
Higher Ed Users Group
This user group is for customers within higher education.
