none
SSIS Source SQL Command from variable

    Question

  • hi,

    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?

    thanks

    Alastair 

    Wednesday, September 28, 2011 11:12 AM

Answers

  • Hello,

     

    In the variables window, set a default value to your variable ("0" for example).

    This way your expression will evaluate as a valid oracle query.

     

    Regards,

    John

    • Marked as answer by sam spaniel Wednesday, September 28, 2011 3:34 PM
    Wednesday, September 28, 2011 12:49 PM

All replies

  • 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
    from msp_pty_special_needs
    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. 


    Lumbago
    www.thefirstsql.com
    Wednesday, September 28, 2011 11:59 AM
  • Hi,

    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.

     

     

    Wednesday, September 28, 2011 12:24 PM
  • I'm guessing that @[User::Sequence] is of type Integer, right? If so, your expression is attempting to concatenate a string with an integer which it cannot do - you need to cast @[User::Sequence] as a string in order to concatenate it.

     


    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    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)

    and

    Provider cannot derive parameter information and SetParameterInfo has not been called. (OraOLEDB)

    Jason,

    yes i have a breakpoint on and yes the variable is being populated with the correct value

     

    Jamie

    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

     

    Cheers

    Wednesday, September 28, 2011 12:35 PM
  • Set DelayValidation property  to true and try to execute.
    Shailesh, Please mark the post as answered if it answers your question.
    Wednesday, September 28, 2011 12:39 PM
  • Hello,

     

    In the variables window, set a default value to your variable ("0" for example).

    This way your expression will evaluate as a valid oracle query.

     

    Regards,

    John

    • Marked as answer by sam spaniel Wednesday, September 28, 2011 3:34 PM
    Wednesday, September 28, 2011 12:49 PM