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?

Regards

SQLSearcher
SQLSearcherAsked:
Who is Participating?
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.
0
SQLSearcherAuthor Commented:
Hi Brian
Can you provide more detail please?

Regards

SQLSearcher
0
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.

 Sample-Lookup.png
Conditional Split
1
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Brian CroweDatabase AdministratorCommented:
Some detailed instructions on how to use the Conditional Split task:

https://www.simple-talk.com/sql/ssis/ssis-basics-using-the-conditional-split/
0
SQLSearcherAuthor Commented:
Hello Brian
How do I setup the Update can you provide more information please?

Regards

SQLSearcher
0
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)

OLE-DB-Command-Task---SqlCommand.pngOLE-DB-Command-Task---Column-Mapping.png
0

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.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.