SSIS Implementation Update,delete,insert
In the database ETL, ETL has two types of data table extraction: All source extraction, which can not identify the changed or new records, incremental extraction, where only new records and changed records.
Here I will briefly explain how to use SQL Server 2005 to set up a service (SSIS) How to achieve all source extraction, if you do not understand, welcome to email and I exchange.
Case:
On the source system, some records are included in the table, which does not identify new or modified records, so the ETL process must compare records between the source and target tables to identify when the change occurred.
Then handle inserts or updates correctly.
Ideas are as follows:
Step one: Use all merge joins and full datasets for the source and destination tables. All connections help identify when inventory records are added to the source or when they are completely deleted.
This requirement is met by using the Merge join transformation configured for all connections.
Note: Because you want to use the merge Join transformation, you must be ordered, and if the order sort component is inefficient in large amounts of data, it can be sorted in advance with order by.
To do this, the data flow requires attention: The source is sorted and the column and orientation applied by the sort are sorted. This is done in the Advanced Editor (Advanced editor) of Source Connection.
On the input and Output Properties tab, view the top-level properties of the OLE DB source output (OLE DB sources), with a property named IsSorted, which needs to be set to True.
Second, you need to specify the sorted columns through the SortKeyPosition property in the Output Columns container. The data stream will then approve this sort.
Step two: Use conditional split split three and a default is as follows:
Insert is null for target table primary key;
Delete is null for the primary key of the source target table;
Unchanged for the source and target table for the change of the difference between each column;
The rest of the update is also the default is the need for update data;