multiline description in flat file
-
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. There are cost figures after the description in each line and the one that contains the description still have these numbers but they are set to zero, here is an 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. Currently we have someone do it by hand, and it takes 3 weeks.
-
One option is to do the *whole thing* in a Script Task. This gives you all the flexibility you need. I've had to do this on a few ocassions where the file is very annoying and the other available tasks in SSIS can't help me.
Another option is to have a script task up front which goes through the annoying file and outputs a new file which is less (or maybe not at all) annoying. You can then use something like a Bulk Insert Task to pump the new file data into the database.
-
Create a data flow task and read each individual row (at this stage do not consider column delimter) Then create a script component task as a data transformation task with the input as the line and outputs as the desired column outputs.
When you process your columns individually check if column 2 in your case is seq no is int, ignore this column if you find string.
For more information refer BOL for Script Component as a transformation.
-
Could you describe how to do it in a little more detail? I was unable to find your reference.
-
I am sorry I am busy today with some production errors but please go trough this example from
http://www.sqlis.com/post/The-Script-Component-as-a-Transformation.aspx
Remember your Input is just one column here.
-
thomasmatthewbrown (4/7/2009)
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. There are cost figures after the description in each line and the one that contains the description still have these numbers but they are set to zero, here is an 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. Currently we have someone do it by hand, and it takes 3 weeks.
If you would consider consulting services, you may contact us. This shouldn't take long to implement in script.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply