Post reply

Dynamic query for Lookup tranformation

  • November 3, 2015 at 2:59 am

    #312745

    Hi

    In my package I am using lookup to get new and similar record.

    I want to filter the rows for Lookup Reference Data Set by using Variable Value

    I have created variable @[User::CustId] with Int32 datatype, having default value 2

    when I am trying to evaluate below query I am getting error

    "select CustId,PartNm,LocId,LocTyp from loc where CustId= "+ @[User::CustId]

    Error.

    The Data types "DT_WSTR" and "DT_I4" are incompatible for binary operator "+".The operand types could not be implicitaly cast into compatible types for the operation. To perform this operation , one or both operands need to be explicitaly cast with the operaotor

  • November 3, 2015 at 3:18 am

    #1837301

    vipin_jha123 (11/3/2015)

    Hi

    In my package I am using lookup to get new and similar record.

    I want to filter the rows for Lookup Reference Data Set by using Variable Value

    I have created variable @[User::CustId] with Int32 datatype, having default value 2

    when I am trying to evaluate below query I am getting error

    "select CustId,PartNm,LocId,LocTyp from loc where CustId= "+ @[User::CustId]

    Error.

    The Data types "DT_WSTR" and "DT_I4" are incompatible for binary operator "+".The operand types could not be implicitaly cast into compatible types for the operation. To perform this operation , one or both operands need to be explicitaly cast with the operaotor

    The error message tells you exactly what the problem is but it's not very clear. You're trying to create your query by concatenating a string and an integer. However SSIS is seeing the '+' sign as addition rather than concatenation so it's causing the error. You need to convert the @[User::CustID] variable to a string using

    (DT_WSTR,n)@[User::CustID] where n is the length of the @[User::CustID] variable.

    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

Viewing 2 posts - 1 through 1 (of 1 total)

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