April 25, 2023 at 7:38 pm
I have a script in the SSIS package that goes to a flat file destination. When I run the script in management studio it runs fine no blank rows. When I use the script in the data flow of my SSIS package it randomly puts a CRLF at the end of a 60 rows and causes a blank row. What can I do to get rid of the phantom CRLF and the blank rows the vendor software does not like the blanks.
April 25, 2023 at 8:16 pm
Here is a small sample of the end of the records with the phantom CRLF
,,,04/09/1991 00:00:00,04/09/1991 00:00:00,,,,SALARIED MANAGER,6328,Y,801
,,,09/18/1991 00:00:00,09/18/1991 00:00:00,,,,PG 105 AFCO LR 1ST,6194,N,201
,,,01/20/1992 00:00:00,01/20/1992 00:00:00,,,,PG 105 AFCO LR 1ST,5970,N,201
,,,01/23/1992 00:00:00,01/23/1992 00:00:00,,,,PG 105 AFCO OFFICE FT PT ND,5908,N,821
The first one and the last one have just a CR and the two in the middle have the phantom CRLF
April 25, 2023 at 9:37 pm
assuming the recipient of the file is able to correctly process a CSV file you need to ensure that the SSIS flat file destination is set to Quote the fields - if that is done the extra CRLF won't cause any issues. Do note that the sample data you provide above is not a valid CSV as it may contain data that required double quotes around values.
Assuming it is unable to correctly process a CSV file (bad bad software) then you will need to replace it on your select from your data.
e.g. on your SQL statement source (or on your SP if that is the source of the data) you do
select xxx
, replace(replace(field_with_crlf, char(10), ''), char(13), '') as field_with_crlf
from tblx
April 26, 2023 at 7:36 am
This does actually sounds like your data does have line breaks and/or carriage returns at the end of the data but you aren't seeing them. Perhaps you're copying the data out of SSMS grid results and don't have Retain CR/LF on copy enabled? Fixing your data would be the better option, and finding out and stopping what ever is adding those characters to the end of your data should be a task to complete shortly after.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 27, 2023 at 1:31 pm
Thank you for you input, that actually worked for me.
April 27, 2023 at 1:34 pm
Thank you frederico_fonseca,
That actually worked for me.
e.g. on your SQL statement source (or on your SP if that is the source of the data) you do
select xxx
, replace(replace(field_with_crlf, char(10), ''), char(13), '') as field_with_crlf
from tblx
April 27, 2023 at 1:48 pm
James Reed wrote:Thank you for you input, that actually worked for me.
what worked? creating a valid CSV file or removing potentially valid data from your output? and if removing valid data did you get approval from the business to do it?
The Replace command you sent. I didnt loose any data.
April 27, 2023 at 3:00 pm
replacing the CR + LF on a text field IS losing data - maybe not important on your case, but it is still data.
In one of the migrations on my customer a developer also decided to do just this (replace) on some data cause it was causing issues with the loader - the data was a multiple line with comments from users - removing them changed the text to be on a single line instead of multiple lines when users were inquiring the data on their UI.
correct solution was, as expected, to ensure the files were valid CSV files, not just a concatenation of values separated by a comma (or other delimiter)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply