i have read numerous posts on this subject but am still encountering problems. In effect what i need to do is select records from a table (Oracle) where a sequence number is greater than the last records I loaded
i have an Execute SQL task which gets the value and sets the value in a variable
i have then created a variable to hold the SQL for the query
EvaluateAsExpressions = True
Expression = "Select party_ref_no, pty_special_needs, start_date, latest_operation, end_date from msp_pty_special_needs where ibmsnap_commitseq > " + @[User::Sequence]
Value shows as Select party_ref_no, pty_special_needs, start_date, latest_operation, end_date from msp_pty_special_needs where ibmsnap_commitseq >
and Value Type is string
Executing the package results in
Can anyone see what might be missing?
What you do is to map the max(seqnr) from the target table first to a user variable in the execute sql task like you say. Then you create a regular oledb source, specify the source as a sql command and then you write the query like this (with the questionmark at the end):
select party_ref_no, pty_special_needs, start_date, latest_operation, end_date
where ibmsnap_commitseq > ?
Then you click the Parameters button next to the query window and specify that the first parameter is the @[User::Sequence] variable.
Add a break point and check if your variable @[User::Sequence] gets any value.
I can't see the atatchment but i'm sure that you need to cast the variable to string as I guess it's a number !
Expression = "Select party_ref_no, pty_special_needs, start_date, latest_operation, end_date from msp_pty_special_needs where ibmsnap_commitseq > " + (DT_WSTR, 50 )@[User::Sequence]
increase the number as what you want...also try to post your error as text so we can see it.
- Proposed as answer by Jamie ThomsonMVP, Moderator Wednesday, September 28, 2011 12:25 PM
thanks for the replies
Lumbago - when i follow your suggestion then, when clicking on the Parameters button, i get an error messgae
Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the "SQL command from variable" access mode, in which the entire SQL command is stored in a variable. (Microsoft Visual Studio)
Provider cannot derive parameter information and SetParameterInfo has not been called. (OraOLEDB)
yes i have a breakpoint on and yes the variable is being populated with the correct value
the variable User::Sequence is a string - the field ibmsnap_commitseq within the where clause is a numeric field so not sure if that requires me to convert the variable to numeric or leave as is because the ValueType is set to string