1

I have a text file in which each record length is varying. I don't care how many columns are there and column delimiter. I just want to make them equal in length using SSIS likely a fixed width file with single column. I want to add space for those records which have less character strength in compare to other. I don't know what would be maximum length in file.

Please share some knowledge with me...

Thanks!

Ankit
  • 13
  • 1
  • 7
  • my first thought is no don't do it. next thought what? Do you want the connection manger to ignore delimiters and import all as 1 field if so just setup the delimters to a non existant combiantion (perhaps pipe |). Or you want to change a delimited file to a fixed width file, then use data flow and add calculated columns that pad cells based on length – Matt Jul 22 '16 at 21:49

1 Answers1

0

Script task to the rescue. Assuming your flat file has uniform delimiters per line i.e. each row in the file are separated by say comma, the way to go about would be -

  1. Drag a Script Task transformation and pass the file path of the input flat file
  2. Start reading the file line by line.
  3. Withing the loop, get the occurrences of delimiter (you can use a Split function to get the count) as a Key and the the whole row as a Value. Store this result set in a dictionary object.
  4. Get the max value of Keys from the dictionary using the Dictionary.Keys.Max() function. (Basically the highest number of delimiter occurrences per line) and store it in a variable say MaxCount.
  5. Now loop through each Key/Value pair. For each line having Key value less than the MaxValue, append empty empty delimiters i.e. say if the Max delimiter per line is 5 and in the line there are only 2, use a for each loop to append additional 3 blank delimiter i.e. ',,,'.
  6. Using a StreamWriter get all the Values of the dictionary into a new file.

You would need to using System.IO and System.LINQ classes to achieve this. When using in SSIS 2008, ensure the Project Framework is set to 3.5 for it to work.

The new file would then be of uniform length.

VKarthik
  • 1,379
  • 2
  • 15
  • 30