CODEWITHSUNDEEP
ssis
user1294510
user1294510

Reputation: 429

Can SSIS check record that is imported during the current run

I am importing multiple users. Each user can belong to multiple companies. So from Source I will get rows like these

userId  companyID  userinfo   companyinfo
123      abc       blah       blah
123      def       blah       blah
456      abc       blah       blah
789      xyz       blah       blah

I have a Lookup Component to check if user already created in the new system. If not create a user in the User Table. If it is, then just add a row in the userid-companyid-relationship table.

My question is does ssis works like that? i.e. it import data row by row. first it import (123, abc, blah, blah). Then when it import (123, def, blah, blah), it realize userid: 123 already in the User Table. Or does it do some kind of bulk import, and anything that is added during the current iteration, the Lookup Component wont be able to pick up, because to him Userid: 123 doesnt exist yet?

Im having some issue with my package, it seems like my lookup component is not working properly, I was wondering if it is because of the concern I just mention.

Hope this make sense. Thanks

Upvotes: 2

Views: 63

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31775

Brian's comment about Cache Mode is spot-on, but I would add that in my experience the best way to handle things like this in SSIS is to import all of the data into a staging table, and then handle the lookups and relationship-creation in a stored procedure on the SQL Server. These kinds of things go much faster in TSQL than they do in SSIS. SSIS is fastest when it's simplest.

Upvotes: 1

Related Questions