Problem with "phantom rows"

Hi,

I am seeing something strange. I have a data file that has 6 rows that looks like:

ABC, "OPENING BALANCE", 1234, etc

ABC, "CLOSING BALANCE", 1235, etc

ABC, garbage data, etc

XYZ, "OPENING BALANCE", 1234, etc

XYZ, "CLOSING BALANCE", 1235, etc

[][] -- weird box things that shows up in my flat file conn mgr

I have a script transformation that reads the incoming rows as a single line, then checks for the value of the row.line, whether it's "OPENING BALANCE", or "CLOSING BALANCE". It ignores all other lines.

I even added a message box that pops up when it finds "OPENING BALANCE" or "CLOSING BALANCE". It only pops up 4 times, like it should.

However, when I check the database, it has 6 rows! The 4 good rows are there, and 2 garbage rows with a bunch of NULLS in them.

I really don't understand how this is happening. Please, any ideas.

Thanks

Hi,

Are you saying to get rid of my script component and use a conditional split instead

I would have no clue how to do this, though!

If you could show an example syntax, I would appreciate greatly.

Thanks

Ok, but then how do I break up the line into columns so that I can map them to my table columns

Use a script component after that

Could you explain how to set up the derived column I don't see how you specify the input or output in this case.

Thanks

Yes, I see how I could do this, if only I could find some DOCUMENTATION on SSIS expressions.

I don't understand why Microsoft creates something that is so specific that only they can provide documentation, but then they don't bother adding any documentation. This is incredibly frustrating.

I don't see one single example of how to use "SUBSTRING", if there even is such a thing, because I can't find ANY information on using string functions in expressions.

Help.

:-(

Ok, after a ridiculous amount of searching, I found the reference page I was looking for.

But I REALLY don't see how I can possibly use substring or findstring to parse my row because it requires that you know what you're looking for first, which I don't.

SUBSTRING(character_expression, position, length)

That is, you have to provide the character_expression which I don't have because each row is different.

Is this what you really mean Because it doesn't look like it's gonna work.

I am just going to use my script, since I can use the index to select which columns I want to use.

I think using an expression to do this would be very complicated, as the values would have to be determined by the number of commas found, or something like that.

Anyways, even before I get to that issue, I am bummed b/c the conditional split isn't working.

This is my expression:

FINDSTRING("OPENING",Column0,1) > 0 || FINDSTRING("CLOSING",Column0,1) > 0

My data viewer shows nothing being sent to the next component after the conditional split.

Any ideas why

Yes, I think the script is easier.

But any ideas why the conditional split isn't working as expected

Thanks

Aha, I see you're just keeping me on my toes :-)

It's like this: FINDSTRING("CLOSING",Column0,1) > 0

character expression comes first, then search string