Updating data using ssis yamada ryosuke dating
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.
when setting up the import task and even when I was trying to use Excel my options seemed limited to simple selects for the source query.
In addition, the OPENROWSET command can also support SELECT statements where a table is joined to the Excel spreadsheet.
Let's work through some examples with the SQL Server 2005 Adventure Works sample database for each operation with a sample Excel spreadsheet.
Are their any benefits to transitioning our code to another approach with the OPENROWSET command?
Yes - You are correct the OPENROWSET command can directly support INSERT, UPDATE or DELETE operations as shown in these tips: Export data from SQL Server to Excel and Different Options for Importing Data into SQL Server.