We can set a row delimiter for a Flat File Connection in SSIS.but the question is how to set multiple row delimiters for a Flat File Connection to not using Script Component for this?
for example for a text file containing this string:
The quick brown fox, jumped over the lazy dogs.
I want to have this two rows after using ',' and '.' for row delimiter:
The quick brown fox
jumped over the lazy dogs
The short answer is that the stock Flat File connection manager doesn't have that capability.
I frankly am having trouble imagining a business situation where somebody is sending me data in a flat file with inconsistent row delimiters. But if I did run into that situation, I'd use a script component, along the lines of @billinkc's answer to your earlier Multiple row delimiters question.
I ran out of room in the comment field or I'd have left this there. It's not an answer as it doesn't work, pity.
Unless of course you have it under version control and roll back if you destroy it.
I don't think it can be done. The next approach I was going to try was mucking with the XML directly. I created a copy of the Connection Manager used in the previous question and fiddled with the dials.
What I observed was the following properties
They were all set to _x000D__x000A_ for the original Connection Manager. 0D0A is CR/LF in hex so I tried _x003B__x000D__x000A_ for my HeaderRowDelimiter. I opened the package up and huzzah, it showed {;}{CR}{LF} on the General tab.
I applied the same to the other properties and things looked promising. I created a data flow, used the new connection manager and it runs, but instead of treating that like an OR it's an AND so I only had 1 row of data flow down the pipeline. I confirmed the AND behaviour by postfixing my first row with a semi-colon and hitting preview. It then showed 2 rows.
Had this worked, to achieve variability you could have employed expressions on the connection manager to set the above properties...
After a quick test, it seems I'd still need to futz with the XML to get the CR/LF to "take." Pasting in the hex code doesn't get un-XMLified in the Connection Manager. You can create an expression on a variable and then get a LF to paste in but I couldn't figure out how to get the CR to show up. Perhaps paste that in from another document.
At any rate, leaving this here, not as an answer to the problem but to provide guidance on what doesn't work. Perhaps it'll spark an idea from a future reader.
You can directly give the delimiters in the box directly, instead of selecting from the dropdown and save it. In my case, I used "$#|" in the text box and it worked fine for me.
