There are countless ways to mix and match components in SSIS to simulate what SQL Server does in a query. Here I am going to show you how to replicate an existence check using SSIS components. The only time I use this type of configuration is when I have cross-server joins. I find it more efficient for SSIS to hop to different servers in discrete components than to rely on SQL Server to do linked server calls. Here is an example of a simple select statement that does a cross-server join to validate the data.
There are many times that I would like to insert a record into a table only if it doesn't exist. This is done easily in T-SQL: