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

A script transformation doesn't block rows. Use a conditional split instead.|||

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

|||A conditional split component just uses expressions to test rows. If the expression evaluates to true, then the row goes down that output path.

Since you are working with the row as one big column, here's a sample config:
OUTPUT NAME: BalanceRows
Expression: FINDSTRING("OPENING BALANCE",[Column],1) > 0 || FINDSTRING("ENDING BALANCE",[Column],1) > 0

Then, back in the data flow, just grab the green arrow and hook it to the next component in line. It will prompt you for which output you want to use. Select the "BalanceRows" output.|||

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?

|||

sadie519590 wrote:

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?

Sure. Or use a derived column and use substrings.|||

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

|||Never mind, I see now|||

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.

:-(

|||When you click on substring in the list of available functions, it tells you how to use it. I know you're frustrated with this, but it's right there.

And for that matter, it's all in BOOKS ONLINE. The first link returned when SEARCHING for "substring ssis" yielded the page you apparently think is missing.|||

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?

|||character_expression is your row, or column since you are reading in the row as one column.

If it's not a fixed width row and positions change, then use the script and however you were going to do it before.|||

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