none
Need help with LOOKUP transform

    Question

  • I have a very simple problem I am trying to solve. 

     

    I have a table with a "DateEntered" field, and I have an ssis pkg set up to load data from a file into the database table. I just want to make sure that no one loads the same file twice in one day.

     

    For example, if today is 8/22/07, and "DateEntered" is "2007-08-22", then I want to add a Lookup transform to run a query that will check and see if there's any rows in the table with a "DateEntered" is "2007-08-22". If so, don't load the file again!

     

    Here's my query:

     

    SELECT Code
    FROM myTable
    WHERE DATEADD(dd, DATEDIFF(dd, 0, DateEntered), 0) = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)

     

    (all the dateadd stuff is doing is removing the time portion from the DateEntered field, so we are comparing apples to apples).

     

    Now, if the query returns a bunch of "Codes" then we know that the data has already been entered for the day! So far, so good.

     

    Now, how do I set up the Lookup to get it to work? I'm getting this error message:

    Error 1 Validation error. Data Flow Task: Lookup [1299]: The lookup transform must contain at least one input column joined to a reference column, and none were specified. You must specify at least one join column. FXRateLoader.dtsx 0 0 

    But I thought I did this! On the columns tab, I have:

    Lookup column: code

    Lookup operation: Replace 'code'

    Output alias: code

     

    I have my error output set to:

    Lookup output - redirect row

     

    I don't know what I'm doing, obviously..........!

     

    Need assistance :-)

     

     

    Wednesday, August 22, 2007 10:28 PM

All replies

  • You need to join a data source flow to the lookup and then join on the key columns.  Perhaps you'd rather use an Execute SQL task to:

     

    SELECT COUNT(*) FROM myTable WHERE DATEADD(.....) and then store the resulting count in a variable.

     

    Then use precedence constraints to control whether or not the data flow executes.  (@CountVar == 0)

    Wednesday, August 22, 2007 10:43 PM
  •  

    I know I can use sql to solve this problem, but I'd rather use the lookup.

     

    I would like to understand the lookup transform. Can it be used here? or is it more effort than it's worth?

     

    From my understanding of the lookup, this seems like a perfect use for it. Or maybe I'm wrong.

    Wednesday, August 22, 2007 10:48 PM
  •  

    ARggh

     

    How do you write this expression:

     

    IF user::count > 0

     

    of course, does not work!

    Wednesday, August 22, 2007 11:00 PM
  •  sadie519590 wrote:

     

    I know I can use sql to solve this problem, but I'd rather use the lookup.

     

    I would like to understand the lookup transform. Can it be used here? or is it more effort than it's worth?

     

    From my understanding of the lookup, this seems like a perfect use for it. Or maybe I'm wrong.

     

    Sadie,

    You have to have a data flow source.  A lookup does not classify as a source.  You need to hook up your source to the lookup transformation and then map the key fields from the source and the lookup table together.

    Wednesday, August 22, 2007 11:03 PM
  •  sadie519590 wrote:

     

    ARggh

     

    How do you write this expression:

     

    IF user::count > 0

     

    of course, does not work!

     

    Where do you want to do this?  In a precedence constraint, it is as I typed it.

     

    Or, and I know we've gone down this path before, @[User::Count] > 0 ? do_so_something : else_do_something

    Wednesday, August 22, 2007 11:06 PM
  • Yes, I have a source that's hooked up to the lookup.

     

    Excel source > Data conversion > Lookup

     

    I am just trying to compare the DataConversion.Code values to the Code values returned by my query. I think I understand the concept. I just don't know how to set it up.

     

    Kirk Haselden's book does not explain it very well at all. I can't find ANY examples online. It's very frustrating.

     

    At this point, I am going to throw in the towel. Problem is, even if I have a valid use for this Lookup, I still don't know how to use it. This is my dilemma. I need some decent examples.

     

     

    Wednesday, August 22, 2007 11:09 PM
  • I have some lookup stuff here: http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed/

     

    I'm sure Jamie has some as well.

     

    I just don't understand where the problem lies.  Isn't the best option for this particular scenario to use an Execute SQL task in the control flow?

     

    Since you are selecting "code" from your lookup table, do you have a matching column in the data flow that you can link up to it?  If so, then what's the problem.  Coming out of the lookup, the green line is where you have matches.  The red line is where the source does not exist in the lookup table.  So in your case, leave the green line alone and don't connect it to anything.  Connect the red line to your destination, or whatever.

    Wednesday, August 22, 2007 11:20 PM
  • The problem is this:

     

    I'm getting this error message:

    Error 1 Validation error. Data Flow Task: Lookup [1299]: The lookup transform must contain at least one input column joined to a reference column, and none were specified. You must specify at least one join column. FXRateLoader.dtsx 0 0 

     

    But I thought I did this! On the columns tab, I have:

    Lookup column: code

    Lookup operation: Replace 'code'

    Output alias: code

     

    I have my error output set to:

    Lookup output - redirect row

     

    I thought I did it right. But it keeps giving me the error msg above.

    Wednesday, August 22, 2007 11:27 PM
  • Right.  That's fine.  BUT did you *map* the input column to the lookup column?

     

    When you go to the columns tab, there are two boxes, "Available Input Columns" and "Available Lookup Columns".

     

    There needs to be a line connecting the two.  So drag "Code" from Available Input Columns to "Code" in Available Lookup Columns.

     

    Wednesday, August 22, 2007 11:58 PM
  • yes, there is a connecting line between the boxes

     

    Thursday, August 23, 2007 12:22 AM
  • Just adding my two cents (after going dark for the last few months) but Phil is spot on here - even if you can do this in the data flow with a lookup, that's like saying "I know I could use a screwdriver to put in this screw, but I really want to use a hammer." I think the missing piece earlier on was that "Count" was the name of the package variable in Phil's example, where you would map the SELECT COUNT(*) value from the Execute SQL task to this value, so you could then use User:Count in the expression on the precedence constraint below.

     

    Would a more explicit example help here?

    Thursday, August 23, 2007 12:30 AM
  •  sadie519590 wrote:
    yes, there is a connecting line between the boxes

     

     

    Can you take a screenshot of the Columns tab and send it to me?  E-mail address is in my profile.

    Thursday, August 23, 2007 12:31 AM
  •  MatthewRoche wrote:

    Just adding my two cents (after going dark for the last few months) 

     

    Nice to see you back here!

    Thursday, August 23, 2007 12:49 AM