Expressions and Datareader Source

Greetings my SSIS friends,

Apologies for asking a similar question again but I am still non the wiser with this problem!

Let me explain to you my situation and the method I've adopted to try and solve it.

I have some source data residing in a SQL Server 6.5 database. The source data consists of a single table. For this example I will assume that my table contains only 2 columns, an ID column called result_ID and a Result_Name.

The idea is to retrieve new data each time the package is run. We will know this because the result_IDs in the source table will be greater than the maximum result_ID in my destination table . The way the package should work is like this :

1) Retrieve maximum result_ID from destination table

2) retrieve data from source table where result_ID > maximum result_ID from destination table.

My package consists of a

1) SQL Query Task which retrieves the maximum result_ID and places it in a user variable (type Int32).

2) A Data flow task with a Datareader source adapter which uses an expression to retrieve the data. My expression looks like this : "select * from result where result_id > " + (dt_str, 10, 1252) @.[User::max_result_id]

When I run my package the first time all the rows are retrieved (as my destination table is empty to begin with). BUT when I run it the second time the same thing happens again!! All rows are retrieved.

I placed a breakpoint at the point where the variable gets populated with the maximum result_ID and true enough, the variable gets populated with the correct result_ID BUT then that variable gets reset to 0 in my expression!

This problem is driving me crazy! Has anybody out there experienced this kind of problem before?! What are the ways to solve it?!

Thanks for your help in advance.

What is the scope of the variable, User::max_result_id?|||

The scpe of my variable is Package so my data flow component shouldn't have a problem accessing it surely.

|||

Phil,

I have just read an interesting article by Kirk Haselden about Variables and their scope and I have now solved my problem! It turns out that I had another variable with a lower scope but with the same name and that was set to 0. In other words my Package level variable was working until SSIS got to the second variable and used that one instead.

I am officially an idiot today.

|||Glad you got it.