Post reply

SSIS 2016 export to pipe-delimited file adding crlf in places it shouldn't

  • May 19, 2017 at 11:17 am

    #325836

    I am doing a simple task of reading a table with columns defined as only varchar (although it may hold numbers, dates, etc. it is still defined in this table as varchar) then exporting that data out to a pipe-delimited file.  I have defined the file manually and expanded the appropriate lengths, etc.  All the field separator's are | with the exception of the last one which is <cr><lf> which is what I would expect.  Nothing in packages except data source and data destination. 

    BUT, it is putting the <cr><lf> in spots it should not be.  I have attached an image of the file results.  The first line I deleted the <cr><lf> as an example of how it should be writing this table and then did the highlighting to show what <cr><lf> should not be there.

    I am utterly baffled at the moment as to why this is happening!

    Thanks,
    Shawn

  • May 22, 2017 at 8:16 am

    #1943563

    Shawn,  I've encountered similar situations when dealing with data that had "lived in" another system at some point (DB2 in my case) and hidden characters were imported into varchar fields.  Generally I've had to write custom SELECTs to manage this using the REPLACE function.

    SELECT LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(CAST(1 as VARCHAR(MAX)))), CHAR(9), ''), CHAR(10), ''), CHAR(11), ''), CHAR(12), ''), CHAR(13), '')))
    + '|' +
    LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(CAST(2 as VARCHAR(MAX)))), CHAR(9), ' '), CHAR(10), ' '), CHAR(11), ' '), CHAR(12), ' '), CHAR(13), ' ')))
    + CHAR(10) + CHAR(13)
    UNION ALL
    SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CAST('1 ' as VARCHAR(MAX)), CHAR(9), ''), CHAR(10), ''), CHAR(11), ''), CHAR(12), ''), CHAR(13), '')
    + '|' +
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CAST('99 ' as VARCHAR(MAX)), CHAR(9), ''), CHAR(10), ''), CHAR(11), ''), CHAR(12), ''), CHAR(13), '')
    + CHAR(10) + CHAR(13)


    Regards,
    Matt

  • May 23, 2017 at 6:56 am

    #1943764

    Thanks, looking into it but the data was hand entered into SQL Server.  but, they might have copy/pasted and picked up erroneous data.

    ~Shawn

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply