SSIS Learning Journey Data Synchronization

Source: Internet
Author: User
Tags ole ssis

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.