1

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?

EzLo
  • 13,780
  • 10
  • 33
  • 38
StackUser
  • 5,370
  • 2
  • 24
  • 44
  • I've used Powershell as an Execute Process task to remove embedded CR/LF from a number flat files in a folder using a for-each loop container. I expect you could also use this to create these? – MiguelH Jun 28 '18 at 09:34
  • My existing row delimiter is {LF}. I want to change it as {CR}{LF}. How do I do this. – StackUser Jun 28 '18 at 10:28

1 Answers1

0

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

enter image description here

MiguelH
  • 1,415
  • 1
  • 18
  • 32
  • I want to change row delimiter from {LF} to {CR}{LF} in the package itself....Is there any way I can do it. – StackUser Jun 29 '18 at 08:13
  • @StackUser The example above is done within the package itself, it simply runs a Powershell script . Chang my example to use `-replace " + ic2 +"`n" + @ic2 + "," + @ic2 +"`r`n" + @ic2 + "`. Test this on one file first! – MiguelH Jun 29 '18 at 12:06