Post reply

Need to read uncommitted data in a Lookup component

  • September 22, 2008 at 2:11 am

    #198273

    Hi,

    I have a package which inserts data into a Parent table and Child table with a foreign key constraint to the Parent tables identity column on the child table. The package inserts data into both the tables in the same data flow. The Child table and the Parent table share another column which is unique for each recordin the parent table. So, i tried to use a lookup to fetch the newly inserted identity values in the Parent table so that they can be used for the foreign key column in the child table using a lookup component. But i find that the query in the lookup doesnt return any row (likely because the data which was inserted in the Parent table is not yet committed as the insertion into the Parent table also happens in the same data flow). How do i read the un committed data (assuming that this is the issue)?

    "Parent table"

    Column NamesDescription

    -------------- -----------

    ParentID Identity

    UniqueIdentifier Some value which is unique for each parent record

    "Child table"

    Column NamesDescription

    -------------- -----------

    ChildID Identity

    ParentID Foreign key to parent table

    UniqueIdentifier Corresponding value in the parent record

    Thanks in advance

  • September 22, 2008 at 5:09 am

    #874876

    First, for the lookup. By default, a lookup component will be in full cache mode. This means that during the pre-execute phase, it will cache all of the data in memory and never re-query the database. So, the data is queried well before your insert happens. You can turn off caching completely in the last tab in the lookup component editor. You could use a query hint to alow you to read the uncommitted data if you still want to go this route. The lookup performance is going to be pretty bad since it will query the table once per row you have in your data flow, and reading uncommitted data is risky.

    A better approach may be to put the child table data into a staging table and create a store procedure to copy the data to the final table and do the lookup of the parent identity value.

  • September 22, 2008 at 6:13 am

    #874909

    Thanks for the reply. I resolved the issue by writing the records to a recordset and then i use the recordset to find the parent records moved.

  • September 22, 2008 at 6:15 am

    #874910

    Please let me know if this is not a good approach

Viewing 4 posts - 1 through 3 (of 3 total)

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