How to create a data synchronization task using SSIS

These packages can be used independently or together with other packages to meet complex business needs. Integration Services can extract and convert data from multiple origins (such as XML data files, flat files, and relational data sources) and then load the data to one or more targets. (From MSDN, for more details, refer to: http://technet.microsoft.com/zh-cn/library/ms141026 (v = SQL .105). aspx)

Next I will use SSIS to demonstrate a practical example. For example, if I have a database, the data of this database will be regularly migrated to other databases for backup data or other purposes. During migration, some new fields will be inserted into the backup database, and some modified fields will be modified in the backup database. Now we will use SSIS to complete this task.

First, run the following SQL statement in my source database db_source and the target database db_destination to create the required table. This table test_1 is used for demonstration.

Copy codeThe Code is as follows:
Create table [dbo]. [test_1] (
[Id] [int] IDENTITY (1, 1) not null primary key,
[Name] [varchar] (50) NULL,
[Age] [int] NULL
)

After the table is created, you can add several records to the source data table. The target database is empty.

Now we open VS and create an Intergration Services Project. (Note: If SQL Server is installed with Express, this project template is not available)

 

After creating a project, drag a Data Flow Task under the Control Flow Tab, for example:

 

Double-click the Data Flow Task and we will enter the Data Flow Tab.

 

Then we find the ole db Source in the left-side toolbar and drag two ole db sources. Name them Source DB and Destination DB respectively.

 

Drag the data source and double-click it to set it. It is mainly used to link the database and select the table or view you want to migrate. I will not detail it here. Note that, as shown in the following figure, if a red X is displayed on a graph, the setting is incorrect.

The next step is to drag two Sort and one Merge Join, point the previous data source arrows to two Sort respectively, and input the data from the last two Sort to the Merge Join at the same time.

Double-click two Sort fields, select the table ID, and Sort the field ID once. Because the Merge Join process requires that the input data be sorted. You can set the SortKeyPosition attribute for the output fields in the data source to sort the data. (See: http://msdn.microsoft.com/zh-cn/library/ms137653.aspx)

The first time we pull the arrow from Sort to Merge Join, we will choose whether the input data is left input or right input, as shown in the figure below, the left is the left input, and the right is the right input. Then we double-click Merge Join and set it as shown in:

The data output after this process is checked here. The Join Type must be Left outer join, because the Left side is our original data table and the right side is our backup table, the right table can be viewed as a subset of the Left table. If the left table has data but the right table does not, the data of the backup database needs to be inserted.

Now we need a branch, that is, the new data needs to be inserted into the backup database, and some data needs to be updated to a new value. We drag a Conditional Split from the toolbar for such branch processing. We direct the output in Merge Sort to Conditional Split, and double-click Conditional Split, as shown in. (Note that one condition is ISNULL and the other is non-ISNULL ).

In this case, their input values are divided into two types of conditional outputs. Finally, we drag an ole db Destination to insert data and an ole db Command to update the database. The final process is as follows:

 

Double-click Set ole db Destionation and select the tables in the target database for data import. Note that you must select the Keep identity option, because I used the auto-increment attribute for the ID field when creating the table.

Double-click Set ole db Command. First, select the link object in the Connection Manager Tab, and then set your SqlCommand attribute in the Component Properties Tab. For example:

 

Are the parameter values used here? And then set the Column in the Column Mappings Tab to replace the actual value, for example:

At this point, the task has been created, and no code has been written. The task is dragged directly. Now you can directly press F5 in VS to see the effect. Our target data table will be inserted into the value in the source data table. Then, let's modify the original data table and run the preceding task to view the changes in the target database.

So how do I regularly complete the task? Here, you can use the SQL Server Agent to call the package we wrote above, and the supervisor uses dtexec.exe to execute the preceding task in the windowsplanning task.