Special Processing on Last Row

Question

Special Processing on Last Row

Bill-290450

SSC Veteran

Points: 246
More actions
October 5, 2009 at 10:34 am

#138540

I'm having an issue in finding the last row in a dataset in order to do some special processing on that row. I'm basically querying an Oracle table and returning a dataset, then writing all of those rows out to a txt file. The last row has to be different, however. I had this working when it was staying within the sql server but requirements were changed so that the source has to come from an oracle table.
When I did this before, I used a sql task outside of the data flow to perform a count(*) on the dataset and set that to a variable. Then, in the data flow, I split the dataset to the Top N and then the Last Row with a script. The problem I'm running into now is that I can't run the count against the oracle db and set the variable. I receive an error about an invalid data type. I have tried several different queries including adding a CAST into the query.
So, is there a way to count records from an oracle table and set the return to a package variable?
Or, is there a way to count the rows in the dataset and use that number all from within the data flow task?
Or, is there a way from within a script to know that I'm on the last row and need to do something different to that row?
Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

There is a row count data flow transformation that can count the number of rows passed through it and then store this count as a variable for later processing..

Bill-290450 SSC Veteran Points: 246 More actions · Answer 2

Can you use the row count transformation, set the variable, and then use the value of the variable all within the same data flow task? I don't think you can set the variable and then use it right after that.

steveb. SSC-Forever Points: 46733 More actions · Answer 3

No you cant use it in the same data flow task, Sorry now that i think a bit more about it I do not think it will be possible with the row-count transformation.

One other way could be to write a custom transformation in vb that can step through the data set and set number each row and mark the final row.

Frank Bazan SSCertifiable Points: 5378 More actions · Answer 4

Hi,

You say you are writing to a txt file. Do you need to write the last row in the same dataflow?

Could you not write the results to file whilst capturing the rowcount in a variable in the first dataflow, then use a new dataflow to write the final row? Or failing that use a script component to append the final rowcount to the file.

HTH

Kindest Regards,

Frank Bazan

Bill-290450 SSC Veteran Points: 246 More actions · Answer 5

At this point, I'm really open to anything. I guess my question would be how do I tell that I'm on the last row in order to write it in the same data flow or a different data flow. If I could do that, then I should be good. I was using the row count to get the number of rows so that I could split out the last row from the rest of the rows.

Thanks.

Bill-290450 SSC Veteran Points: 246 More actions · Answer 6

Also, I already have a script in place that adds a surrogate key to each row so that I can then split out the last row. Is there some way in that script to tell that I'm on the last row? I was looking at the row.EndOfRowset but wasn't sure if that would work for this situation.

Thanks again.

Frank Bazan SSCertifiable Points: 5378 More actions · Answer 7

I don't really understand how you're using the surrogate key or why you need it, but adding a rowcount to the final row of a text file is easy enough...

1) Perform the 1st dataflow writing the results to the txt file capturing the rowcount in an SSIS variable using the rowcount component.

2) Then create a 2nd dataflow that will use the same txt file as a destination. Create a source OLE DB component with a SQL command from a variable (you can use the same connection manager you used for the 1st dataflow). Build a sql command from an expression using your newly populated rowcount variable. (It will look something like "SELECT " + @Row_Count + " AS MyRowCount" ) - this will return a single record which you can insert into the FlatFile destination. There is a flag on the flatfile destination editor that allows you to overwrite or append the records. If you uncheck this for your second dataflow then you should be able to add one record to the end of the file.

You may need to create a separate new flat file connection manager for 2nd dataflow pointing to the same file as it will have a different metadata definition.

I hope this makes sense. I don't have an environment to test this on right now, but I think this is the simplest way to make this work without any code at all.

HTH

Kindest Regards,

Frank Bazan

Siddharth Mehta Say Hey Kid Points: 690 More actions · Answer 8

A very simple but not so efficient solution can be, count the number of rows coming out of the flow and store in a user variable. In the Script component, keep a counter and keep comparing it with the user variable the holds the count to determine when the last row is received. On encouterning the last row, just route it to a different output flow or path.

Regards,

Siddharth Mehta