March 5, 2010 at 2:16 pm
When I receive a particular flat file, I extract some data and insert it into a "master" table. Then, I want to lookup the PK for the record just inserted and use it as a foreign key as I insert new entries into two other tables. However, the lookup immediately following the first insert always fails.
The record does exist in the master table. I can select and display it. The lookup definitely has the correct values for the lookup
March 5, 2010 at 3:49 pm
Could it be that the record in the master table has not been committed?
What about batch size?
Try going into debug mode, set break points and after the Insert into the Master Table, query the table from another session.
You may have to disable constraints or something but chec k the table and create a Log File, etc.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 6, 2010 at 9:35 pm
If you can show the package execution error that would be helpful. Are you certain the fields you are doing the lookup on will only return 1 matching row? You can test this by running the select manually via mgmt studio. You can also set up error handling to redirect error rows to an different table and then look for common threads in the data.
March 8, 2010 at 9:19 am
I have Enterprise manager open and a query successfully returns a single row of data based on the lookup. It seems that the package is running in some other context.
March 8, 2010 at 9:28 am
The error shows "Lookup failed". I have an enterprise manager session open and after verifying the lookup parameters in a data viewer, I query the master table and a single row returns. Hmmmm
March 8, 2010 at 9:31 am
what is the type of data that you are looking up? if it is String data then the lookup will be case sensitive by default and will fail if it cannot find the match.
Also you can setup an error data-flow and send the failed lookup to a flatfile to see what exactly is failing
March 8, 2010 at 9:52 am
Two strings, one is 9 chars with only a single letter in it (and the case is correct), the other is 4 chars, all numeric.
ex: HeatNo = 803L25160
Login = 1405
The record is there prior to the lookup if I query the database from enterprise manager, but the data flow can't find it.
March 8, 2010 at 10:19 am
It's difficult to tell from a partial screenshot and without seeing what's in any of these items, but it looks as if you've set all of this up within a single data flow.
By default a data flow operates by fully caching the lookup query results as soon as the data flow is executed so that it only has to execute the SQL inside the lookup once, which is beneficial if you're dealing with thousands or millions of rows requiring a lookup.
If you look at the properties of the Lookup Object, it has a property called CacheType with the options Full/Partial/None - in your case, you would need None for this to work, however this would be very slow as it would execute the lookup query for every row in the dataflow.
A single data flow is probably not the best approach for what you're trying to do if you're doing a large number of inserts from your csv.
Based on the assumption that you have to do the insert to get the PK (e.g. the PK is an identity column) you'll get much better performance if you can think of a set-based way of returning all the PK's affected by the first data flow step and running a second data flow to perform your second batch of operations.
March 8, 2010 at 10:29 am
Thanks. I read about caching on Friday and thought it might be the answer but I also read about the performance issue. At the end of the day Friday I split the task into two control flow tasks. The first does the insert to the "parent" table and the second does parallel inserts into the two "child" tables. Of course, both child data flow tasks are doing lookups which is somewhat inefficient. I could add another control flow task to lookup the fk and save it in a variable for use by the child data flows.
September 4, 2011 at 3:51 pm
Hi all,
got the same issue.
LOOKUP ID
Lookup Not Matched (1 Row)
OLE Command -> SET MISSIN ID VIA INSERT INTO (1 Row Inserted)
LKP -> LOOKUP ID _AGAIN
/ / / nO mATCH rEDIRECTE TO Error Output ->(1 Row)
/
Matched Output -> (0 Row)
--> at this point it should go to matched and return the inserted ID... that was my assumption
September 4, 2011 at 4:32 pm
Hello Folks,
Ok, i´ve solved the issue by setting OLEDB (BEGIN / COMMIT TRANSACTION) and setting the Lookup CacheMODE to NO Cache.
I Do Not Know if the Begin End Transaction for my insert statement is neccessary. it Works for now.
Cheers:-D
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply