CODEWITHSUNDEEP
ssislookupforeach-loop-container
Nitin More
Nitin More

Reputation: 1

SSIS Lookup transformation used inside for-each loop gets stuck

I have a SSIS package which load currencies data from source file to sql server table.Using foreach ADO enumerator using ADO object source variable to get the list of currencies from source file and inside foreach loop using lookup transformation to have currencies lookup and load non matched data into db table. Package executes the first currency iteration data into db table but for second currency it gets stuck. when checked for any blocking i saw Schema stability lock causing the issue. Not sure how can i fix this.

Things that i tried.

  1. Tried changing the option from full cache to partial cache and no cache but same.
  2. Tried using Stored procedure to load the lookup data instead of direct table in lookup transformation but same.
  3. Checked for the blank or null values in the list of currencies loaded inside ADO enumerator object variable, Where i found one blank value . Removed it and ran the package but still getting stuck.
  4. I found that the for each loop stucks only when there is no matched data PRESENT to load . If NO records are present to load, then the package runs fine.

Expecting that the foreach loop should pick each currency data one by one and load records into db table when there is no match found in lookup.

Upvotes: 0

Views: 55

Answers (0)

Related Questions