May 21, 2014 at 8:19 pm
I have written a stored procedure to create data to a table. I have one column that is giving me issues in SSIS when I try to move the data to a .csv flat file. Any help would be greatly appericated. My is is layed out below.
After the SP runs here is the data element in the table: (looks fine)
18 Love Rd PO BOX 255 (This is an example.)
Then when the data flow task runs and I go to the Flat File Connection Manger to preview the file it appears like this: 18 Love RdPO Box 255 forcing the RdPO together in the preview.
Then when I go look at the file output it causes a line break in the output:
The file should read firstname, lastname, address, city, state, and so on
But the file read like this:
John|Smith|18 Love Rd
PO BOX 255|Nashville|TN|and so on
So it is casusing the field to split and move to a new line.
Any suggestions would be greatly appericated. Thanks in advance.
May 22, 2014 at 12:16 am
It seems there are carriage returns in your data.
Simply add the following to your stored procedure:
REPLACE(Address,CHAR(13)+CHAR(10),'')
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 22, 2014 at 3:20 am
The sample data is only one column data or total one row and my suggestion is try to use LTRIM and RTRIM when populating data to file and vice versa.
May 22, 2014 at 3:24 am
Honny (5/22/2014)
The sample data is only one column data or total one row and my suggestion is try to use LTRIM and RTRIM when populating data to file and vice versa.
LTRIM and RTRIM won't remove any carriage returns in the middle of the data.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 22, 2014 at 3:46 am
You can handle new line or carriage return at source (Stored Procedure) itself as suggested by Koen.
But still as you mentioned "SSIS Help with flat text file issues" suggests that you are using the SP written by someone else.Well you can handle this in SSIS as well using Derived Column transformation where you can use below expression.
REPLACE(REPLACE(Column,"\x000A",""),"\x000D","")
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
July 15, 2014 at 7:49 pm
It seems there are carriage returns in your data.
Simply add the following to your stored procedure:
REPLACE(Address,CHAR(13)+CHAR(10),'')
+1
But still as you mentioned "SSIS Help with flat text file issues" suggests that you are using the SP written by someone else
OP mentioned it is a procedure that he wrote. One thing to note is that doing the update at the source with your stored procedure is more efficient especially if you are dealing with a great amount of rows as opposed to doing the change in the data flow.
----------------------------------------------------
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply