We always upload the data to a table and then begin the process.Although this process works are you familiar with any other options to directly perform the INSERT, UPDATE or DELETE operations?This has been beneficial for us because in our environment because our business users provide data to us in the form of Excel spreadsheets.Many of the situations end up translating to INSERT, UPDATE or DELETE code in one or more of our SQL Server databases.Working with an ETL design that does a straight insert operation is the most simple, but often ETL processes are required to update data as well.There are several ways to go about updating data with SSIS, one of which (the staging table method) requires a bit more setup but can also perform significantly better. I'm new to SSIS and need a suggestion on which way to proceed to resolve the following problem.I can lookup the documentation and implement it, just need to be pointed in the right direction within the SSIS framework.
From the staging table I need to update or insert data in the users table, depending on whether or not the user already exists.
I really need to be able to run a full script that checks the first column (which is my key), updates existing records, then adds any new ones.
Maybe I am taking the wrong approach here altogether, but I thought there was a way to do this with SSIS. If it were two tables and I didn't have to use SSIS I would just write the sql and be done with it.
One way of doing what you are talking about is to use a lookup component to determine if a record exists, in the lookup failure you will get missing records - put your insert here, and in the lookup success you get existing records - put your update here.
In addition to that, you can use a merge join to determine if records exist by using a left join in the merge join and then using a conditional split after it looking for NULL's.