This chapter has been written by others, but I think it's better to write.
Data synchronization is actually to imitate the database's publish subscription function
Chapter One: SSIS Learning Journey First SSIS Example (i) (top)
Chapter Two: SSIS Learning Journey The first SSIS example (ii)
Design:
The previous two examples have created two databases, Demon and Demon_text, respectively.
There are the users table in the Demon Library users table as the main table to synchronize data with the Demon_text Library users table
regular data processing makes two of library data consistent
To create a database script:
--Create a database demoCREATE DATABASEDemon--Create a user tableCREATE TABLE [dbo].[Users]( [ID] BIGINT IDENTITY(1,1) not NULL, [UserName] NVARCHAR( -)NULL, [Passwords] NVARCHAR( -)NULL) on [PRIMARY]--add data to a tableINSERT into [dbo].[Users]VALUES('Lilei','123456')INSERT into [dbo].[Users]VALUES('wangmingmign','654321')INSERT into [dbo].[Users]VALUES('Jack','123654')--Create a databaseCREATE DATABASEDemon_text--User TableCREATE TABLE [dbo].[Users]( [ID] BIGINT IDENTITY(1,1) not NULL, [UserName] NVARCHAR( -)NULL, [Passwords] NVARCHAR( -)NULL, [Usergroupid] BIGINT NULL) on [PRIMARY]INSERT into [Users]VALUES('Lilei','123123',1)INSERT into [Users]VALUES('wangmingming','123456',2)
Create statement
Because the library used by this package is a database of the above two chapters in order to create a database connection manager without repeating
I set the connection manager for the Demon Library and the Demon_text Library to a project connection
Named sub-tables changed to Demon and demon_text to make it easier to distinguish
1. First create a data Flow task.
2. Create two OLE DB sources to point to the Demon Library and the Demon_text library, respectively
3. Create a Merge Join
Note: Because there is a delete operation from the library. So select the full outer join. According to the actual situation to choose
4. Create a Conditional split
Split data according to different dimensions.
5. Create an output OLE DB destination
6. Creating an OLE DB command (for modifying operations)
7. Create an OLE DB command (for delete operations)
The delete operation is the same as the modify operation. It's just a different SQL statement.
8. Run the project to
I do not recommend this form to synchronize data from tables in the database.
Because there is a feature for publishing subscriptions in SQL Server.
SSIS Learning Journey Data Synchronization