SSIS reading from Record set instead of Database

问题:

I'm doing some data migration of a large amount of data in which I need to perform some data matching in order to identify the operation that needs to be done on the record. For that What I'm currently doing is to read the data from the source and then match the records using a SQL Command - so that I need to hit the Database twice for each record. So Will it improve the performance if I read the data to a recordset and then match the values inside that ?

I'm reading from SQL Server 2008 R2

回答1:

1) using Look up transformation is one efficient way of merging records

ex:

2) Use merge procedures

ex:

MERGE [dbo].[Value] AS TARGET
         USING [dbo].[view_Value] AS SOURCE 
        ON ( 
            TARGET.[Col1] = SOURCE.[col1]  

            )

     WHEN MATCHED 
     THEN
     UPDATE SET
     TARGET.[col3] = SOURCE.[col3]
     TARGET.[col2] = SOURCE.[col2] 

    WHEN NOT MATCHED BY TARGET THEN 
    INSERT ([col1], [col2], [col3]  )
    VALUES (SOURCE.[col1], SOURCE.[col2], SOURCE.[col3]  )