April 20, 2011 at 1:21 am
Hi,
I have a table, which has Comments Column. In that i am getting Carriage return. I have used row delimeter as comma separater and column delimeter as {CR}{LF} [Carraige return]. While using the Flat File Connection Manager, this Comment column comes as two separate rows rather than single row.
How can be handle this in SSIS?
April 20, 2011 at 2:38 am
Does the comments column have a text delimiter? I mean, is it surrounded by quotes, perhaps?
Without this, I cannot see how SSIS could ever distinguish between a new row (CRLF) and an embedded CRLF.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 20, 2011 at 3:02 am
It is the plain text. No quotes in between. It is something like below...
This is
sample text.
April 20, 2011 at 3:33 am
Then you are up a certain creek and short on paddles.
As I mentioned, how can SSIS distinguish between an embedded CRLF and an end-of-row CRLF?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 20, 2011 at 4:31 am
Even I really don't know how to check the identity of these. Also that is what my clarification is....
April 20, 2011 at 4:45 am
I just read your original post again and maybe I misunderstood what you are doing.
Are you trying to export to a flat file or import from a flat file?
If you are exporting, you should be able to run a REPLACE on the text in the column to get rid of CRLFs before you do the export.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 20, 2011 at 5:57 am
Thanks Phil.
I am importing data from a flat file. In my flat file the text data is something like This is
sample text
This Flat file comes from a third party tool in which we are not able to do anything. Either we have to do something using code or in thr flat file connection manager i suppose. Please advice.
April 20, 2011 at 6:16 am
If your data looks like this (for example):
ID, Text, Date
1, Good record, 2011-01-01
2, Bad rec
ord, 2011-02-02
3, Another good record, 2011-01-3
4, A very bad
bad
bad
record, 2011-04-04
You are going to have to pre-process the file before SSIS can handle it. Whether you can come up with some logic to remove the unwanted CRs depends on the structure of the file (eg, in the above, any line which does not begin with a number is definitely one which needs processing).
By the way, I think that embedded commas will also cause you pain and you may have to include logic to replace these too in your code.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 20, 2011 at 6:34 am
Thanks Phil. Let me try as you suggest.
April 20, 2011 at 7:01 am
Good luck and happy coding! 🙂
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply