Using SSIS to create a synchronized database Data task __ Database

Create a synchronization database data task using SSIS

SSIS (SQL Server integration Services) is a platform for generating enterprise-class data integration and Data transformation solutions. Using Integration Services solves complex business problems by copying or downloading files, sending e-mail to respond to events, updating the Data Warehouse, purging and mining data, and managing SQL Server objects and data. These packages can be used independently or in conjunction with other packages to meet complex business requirements. Integration Services can extract and transform data from a variety of sources, such as XML data files, flat files, and relational data sources, and then load the data into one or more destinations. (excerpt from MSDN for more details: http://technet.microsoft.com/zh-cn/library/ms141026 (v=sql.105). aspx)

Let me use SSIS to illustrate a practical example. For example, I have a database that, for backup data or other purposes, periodically migrates data from this database to other databases. When migrating, some of the new fields are inserted into the backup database, and some fields that have been modified are also modified in the backup database. Now we're going to use SSIS to accomplish this task.

First, in my source database db_source and the target database db_destination run the following SQL to create the necessary tables, this table test_1 to demonstrate.

CREATE TABLE [dbo]. [Test_1] (
    [Id] [int] IDENTITY (1,1) NOT null primary key,
    [Name] [varchar] () null,
    [ Age] [int] NULL
)

The table in the Source data table can add a few records, the target database temporarily empty.

Now we open vs and create a intergration Services Project. (Note: If SQL Server is loaded with the Express version, there is no project template for this item)

After creating the project, drag a data flow Task under the Control Flow tab, as shown in the following figure:

Double-click the data flow Task and we'll get into the tab tab of the data flow.

Then we found OLE DB source in the left toolbar and continued to drag two OLE DB source out. They are named source DB and Destination DB, respectively.

After dragging the data source out, double-click it, you can make some settings, mainly linked to the database and select the table you want to migrate or view settings, and so I do not elaborate. Note that as shown in the figure above, if a red X appears on a graph, the error is set.

Again, drag the two sort and one merge join, point the previous data source arrows to two sort, and the last two sort data to enter the merge join at the same time.

Double-click Two sort, check the ID in the table, and sort the field of ID. Because the merge join process requires that the data entered is sorted. This sort can also be sorted directly in the data source for their output field settings sortkeyposition this property. (For details: http://msdn.microsoft.com/zh-cn/library/ms137653.aspx)

The first time we go from the sort pull arrow to the merge join, we're going to choose whether the input data is left or right, as shown in the diagram, left as input, and right as input. Then we double-click the merge Join and set it as shown in the following illustration:

The hook here is the data that is output after the process, and the join type needs to be selected as the left outer join, because the left-hand side is our original datasheet, the right side is the table we backed up, and the right table can be seen as a subset of the left table, if there is data on the left table Those are the data that needs to be inserted into the backup database.

Now we need a branch where the new data needs to be plugged into the backup database, and some data needs to be updated to the new value. We drag a conditional split from the toolbar to do the branch processing. We point the output in the merge sort to conditional split, and then double-click Conditional Split, as shown in the following figure (note that the condition is isnull, one is IsNull).

At this point their input values are divided into two conditional outputs, and finally we drag an OLE DB destination to insert the data and an OLE DB command to update the database, and the final process is as follows:

Double-click to set OLE DB destionation, select the table in the destination database for the data import, and here is the option to hook the keep identity, because I used the self-added attribute for the ID field when I created the table.

Double-click to set OLE DB Command, first select a good link object in the Connection Managers tab, and then set your SqlCommand properties in the Component Properties tab. The following figure:

The parameter values here are all used. Number instead, and then in column mappings This tab, set the columns that replace the value instead, as shown in the following figure:

At this point, the task was created, no code was written, and the drag was done directly. Now you can just press F5 in VS to see the effect, and our target datasheet will be inserted into the value in the source datasheet. Then we modify the original data table, and then run the above task, you can see the changes in the target database.

So how to get the task done regularly. Here you can use the SQL Server agent to invoke the packages that we have written above, or use DTExec.exe to perform the above tasks in Windows planning tasks.