SSIS Update records if exist insert new record if not

Hello Experts Exchange
I need to develop a SSIS package that will update a table if a record exists, if a record does not exist then insert the record.

However if a record does exits and only update the record if the field Verified is null and the field Populated = Y.

I think I need to use a Foreach Loop Container to be able to do this check on each record, but I don't know how to configure.

How would I configure the SSIS package to do this?


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
I assume you are using a Lookup task to determine if the record exists.  Just route the Match Output to a Conditional Split Task to check the Verified and Populated table and send those rows to an OLEDB command task to update the records.

If you need more detail on how to set these up let me know.
SQLSearcherAuthor Commented:
Hi Brian
Can you provide more detail please?


Brian CroweDatabase AdministratorCommented:
Here is an image of part of a package that I use.  It is performing a lookup on dimProject and sending all matched data to the Conditional Split which is pictured below in more detail.  My example is comparing checksum values but you could easily change it to check your Verified and Populated values.

Hopefully this makes it a little clearer.

Conditional Split
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Brian CroweDatabase AdministratorCommented:
Some detailed instructions on how to use the Conditional Split task:
SQLSearcherAuthor Commented:
Hello Brian
How do I setup the Update can you provide more information please?


Brian CroweDatabase AdministratorCommented:
The UPDATE in this case is an OLE DB Command Task.

Provide the Connection Manager on the Connection Managers tab
Enter a SQL UPDATE command in the SqlCommand property on the Component Properties tab (image below)
Map query parameters to input columns (image below)


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SQLSearcherAuthor Commented:
Thank you for your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.