"TableLoader" is a custom component for SQL Server Integration Services. It can be added to the Data Flow of an SSIS-package to merge (insert + update) data into tables.
In addition this component can automatically map Data Flow columns to target-table columns based on their names.
- Microsoft VisualStudio 2010 or greater
- Microsoft SSIS 2008R2 / 2012 / 2014
Input-data for initial run:
| pk | last_name | first_name | hobby |
|---|---|---|---|
| 1 | Smith | John | Rodeo |
| 2 | Miller | Max | Football |
Target-table after initial run, containing merged (inserted) data written by TableLoader:
| pk | last_name | first_name | hobby |
|---|---|---|---|
| 1 | Smith | John | Rodeo |
| 2 | Miller | Max | Football |
Updated input-data for second run:
- No data for John Smith (nothing changed here)
- Max Miller has a new hobby
- New entry William Wilson
| pk | last_name | first_name | hobby |
|---|---|---|---|
| 2 | Miller | Max | Tennis |
| 3 | Wilson | William | Volleyball |
Target-table after second run, containing merged (updated and inserted) data written by TableLoader:
- unchanged John Smith
- updated Max Miller
- new William Wilson
| pk | last_name | first_name | hobby |
|---|---|---|---|
| 1 | Smith | John | Rodeo |
| 2 | Miller | Max | Tennis |
| 3 | Wilson | William | Volleyball |
- open SSIS Package
- create DFT and open it
- insert a src-component that gets the data you want to merge via TableLoader into your target-table (i.e. described Input-table)
- find TableLoader component in SSIS Toolbox, add it to Data Flow
- connect output of src-component to input of TableLoader component
- open and configure TableLoader component:
- on tab "Configuration":
- set an ADO.NET connection manager to your target database
- set "TL Type":
- FastLoad: uses multithreading; you can not use it within a transaction.
- TxAll: no multithreading; you can use it within a transaction
- chose the destination table (i.e. dbo.hobby_table)
- set DB Command to "Merge (table based)"
- on tab "Mapping":
- press "Auto Map" to map input columns to output columns (based on their names)
- select which columns are used to insert and/or update data
- at least one "Key" column must be selected to merge data (typically the primary key). Key columns specify the matching columns on which input table is joined with output table.
- done
If you find a bug, please contact us on GitHub
2016-04-15 First Release