Multirow description field

[DowntownD17]

I have an annoying flat file that I'm trying to use SSIS to get into a database. The thing I can't figure out is that the flat file has a weird convention, if the row has no seq control number, the description is longer than one row and needs to be append to the description above it. Here an example:

1 This is a line
2 This is another lin
3 This is the line that never ends
it could go on and on my friend...
4 An yet another line

So 3 and the line after need to appended together, any ideas. (PS There are description that take up more than two rows.)

 

[RiverGuy]

Use a Script Task to parse and format your file properly.

 

[DowntownD17]

Okay, how would you write a script task to do this, the columns are fixed length if that helps.

 

[RiverGuy]

You drag out a script task from the toolbox and add VB.Net code to it. I suppose the easiest solution would be to replace all numbers at position n with a NewLine. You should then be able to set NewLine as your record delimiter.

 

[MDXer]

how does your existing process currently handle this?

 

[DowntownD17]

That is not going to work the description is not the only thing in the line. There is cost figures in each line and the one that contains the description still have these numbers but they are set to zero, here is a more acurate example of the data.
1234 1 Description1 3.33 4.00 8.9
1234 2 Description2 4.21 3.12 9.1
1234 Continuation of 2 0.00 0.00 0.00
1234 3 Description3 8.89 2.11 3.22

I was thinking of making use of a scripted task before but couldn't figure out how to read the current row and still look ahead at the next row. It seems I would need a copy of the row class passed to though the parameter but I not sure if I can do this. Sorry, I should have been more descriptive to start with. Currently we have someone do it by hand, and it takes 3 weeks.

 

[RiverGuy]

If you were a carpenter building a house, and someone showed up with a couple of trees for you to use as wood, you could attempt to cut them to boards with your circular saw, but it would be better to prepare them ahead of time at the saw mill.

Same situation here. You need to prepare your data source to be something more manageable. The first thing which comes to mind is to write an application to do this. The language isn't really that important. You could use a script task in SSIS where you have access to the .Net libraries, or you could create a stand alone application in a .Net language for your job to call, or you could write a C application to format your source files.

I was thinking of making use of a scripted task before but couldn't figure out how to read the current row and still look ahead at the next row

Here, it would be easiest to read all lines into a string, where you'll have access to an array of strings, allowing you to move back and forth easily. Here's an explanation of that function:

http://msdn.microsoft.com/en-us/library/system.io.file.readalllines.aspx

 

[MDXer]

Have you had talks with the owners of the source system to see if the data is in their system and if so why? Sometimes knowing the upstream elements can't help you work through your issues. Who knows maybe they may have a better solution.