SSIS change column delimiter in file

• adamcurtis

  Old Hand

  Points: 338

  More actions

  January 31, 2013 at 3:40 pm

  #394083

  SSIS SQL 2008

  My objective is to monitor a directory for new CSV files, output a new file that replaces the commas in the CSV with | (pipes) and changes from a CSV extension to TXT, then archive the unmodified/original CSV file.

  My directory structure looks like this:

  -MainDropFolder (where initial CSV is dropped)

  --Archive (where the original CSV file will be moved to from Processing at end of package)

  --Processing (where CSV from main drop gets moved to at start of package)

  --Ouput (where the new TXT file with | delimiter should be placed)

  I have built the package accomplishing all of these steps except the changing of the delimiter. My current Control Flow looks like this:

  1. Script Task (Watch for incoming file)

  2. File System Task (Move data file to processing directory)

  3. File System Task (Copy to Output and rename as txt)

  4. File System Task (Move processed file to Archive directory)

  What is the best way to go about changing the comma delimiters to | (pipes)? I'm thinking it needs to be done within another Script Task, but I am not well-versed in C# to accomplish this.

• Koen Verbeeck

  SSC Guru

  Points: 259165

  More actions

  February 1, 2013 at 1:26 am

  #1583293

  This thread has several solutions:

  http://stackoverflow.com/questions/7243751/how-replace-all-spaces-in-a-text-file-with-one-character

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  February 1, 2013 at 6:16 am

  #1583411

  And here is an article on how to watch a directory for incoming files using a Script Task.

  Using the Script Task in SSIS to Process Data Files When They Arrive[/url]

  The articles uses Excel files in the demo but it accepts a file-mask so just adjust the variable values to suit your needs.

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• adamcurtis

  Old Hand

  Points: 338

  More actions

  February 1, 2013 at 7:20 pm

  #1583628

  Thanks, Koen. I'll see if I can use a suggestion in that thread. And OPC, that was the exact article I used to build the watcher task. The code he used was only valid for 2012, not 2008, but he posted the code for 08 in the comments. Very helpful.