SSIS: Change row delimiter from {LF} to {CR}{LF} massively in flat file connection manager

Question

I am new to SSIS, I have created a data flow with oledb source and flat file destination.

Initially the destination file have row delimiter as {LF} but I have to change it as {CR}{LF} now. I have around more than 100 flat file destinations like this.

I tried the following approaches, the second one works but it is time consuming process.

1. I tried by opening each flat file connection manager and try to change the row delimiter but my visual studio is not responding. I did several times but no luck.

2. I deleted the flat file connection manager and re-create it with right row delimiter then its working fine but my concern is I have to do it for more than 100 times.

3. I opened the .dtsx file in a text editor and I can find header row delimiter but unable to find row delimiter.

4. I try to change the row delimiter in the expression but it does not take into effect.

Is there any best way we can simply do this?

Answer 1

I used this to remove CRLF

"$text = [IO.File]::ReadAllText(" + @ic + @FullFilePath + @ic + ") -replace " + 
ic2 +"`r`n" + @ic2 + "," + @ic2 +" " + @ic2 +  "; [IO.File]::WriteAllText(" + 
@ic+ @FullFilePath + @ic + ", $text)"

where 
@ic = ' 
@ic2 = """
@FullFilePath is the path returned from the For..Loop container.

Note: I copy the original file to a new folder and update the copy rather than modify the original.

I expect this would work for you if you change this code:

-replace " +  ic2 +"`r`n" + @ic2 + "," + @ic2 +" " + @ic2 +  "

to

-replace " +  ic2 +"`n" + @ic2 + "," + @ic2 +"`r`n" + @ic2 +  "

I developed this in VS 2008. screenshot