The most efficient way to merge data into a SQL Server database from an ODBC source

Question

I need to constantly merge (update / delete) data from an ODBC data source into a SQL Server 2008 database (the number of rows varies from one row to 100,000 rows)

What would you recommend as the most efficient approach (using .net 3.5):

• Use SqlBulkCopytemp in the table, then call the stored procedure using the Merge command, using the temporary table as the source.
• A stored procedure call with a table value parameter, where data is sent as parameter ( SqlDbType.Structured), a table parameter used as the source of the merge command. Are the data sent using the table parameter sent to the server in a bulk operation? Is it possible and efficient to use it in cases where there are 1000 lines?
• Call a stored procedure using the merge command, which uses OpenRowsetbulk to retrieve data from an ODBC source (use a linked server?)
• Any other way.

Thank!

Answer 1

, . 10 -, 1, SSIS, , 1. JNK , , , , .

SSIS :

• ,

SQL. DontSaveSensitiveData, , .