Use SSIS to create a synchronization database data task-Alibaba Cloud Developer Community

2014-11-18 854

introduction: original text: use SSIS to create a synchronization database data task SSIS(SQL Server Integration Services) is a platform for generating enterprise-level data Integration and data conversion solutions. Integration Services can be used to solve complex business problems, such as copying or downloading files, sending emails to respond to events, updating data warehouses, clearing and mining data, and managing SQL Server objects and data.
+ Follow to continue viewing
original: use SSIS to create a synchronization database data task

SSIS(SQL Server Integration Services) is a platform for generating enterprise-level data Integration and data conversion solutions. Integration Services can be used to solve complex business problems, such as copying or downloading files, sending emails to respond to events, updating data warehouses, clearing and mining data, and managing SQL Server objects and data. 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 sources, 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 information, see: http://technet.microsoft.com/zh-cn/library/ms141026(v= SQL .105).aspx )

 

the following is an example of SSIS. For example, I have a database that periodically migrates data from this database to other databases for backup or other purposes. During migration, some new fields are inserted into the backup database, and some modified fields are also 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 destination database db_destination to create the required table, and use this table test_1 as an example.

CREATETABLE[dbo].[test_1](
    [Id][int]IDENTITY(1,1) NOTNULLprimarykey,
    [Name][varchar](50) NULL,
    [Age][int]NULL
)

 

After the table is created, you can add a few records to the source Table. The destination database is left blank.

Now Open VS and create a Intergration Services Project. (Note: If SQL Server is installed with Express version, there is no project template for this project)

 

after creating the project, drag and drop a Control Flow under the Data Flow Task Tab, as shown in the following figure:

 

double-click the Data Flow Task and the Data Flow Tab appears.

 

Then we find the OLE DB Source in the toolbar on the left and drag the two OLE DB Source out. Name them Source DB and Destination DB respectively.

 

Drag the data source and double-click it. You can set it to link the database and select the table or view you want to migrate. I will not go into details here. Note that, as shown in the preceding figure, if a red X appears on a graph, the settings are incorrect.

Next, drag two Sort and one Merge Join, point the previous data source arrows to two Sort respectively, and input the last two Sort data into the Merge Join at the same time.

 

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

here, when we pull the arrow from Sort to Merge Join for the first time, we will choose whether the input data is left input or right input. As shown in the figure, the left one is left input, the input on the right is the input on the right. Then double-click the Merge Join and set it as shown in the following figure:

 

the data output after this process is checked here. Join Type need to select Left outer join, because our original data table is on the Left and the table backed up is on the right, the right table can be considered as a subset of the left Table. If the left table has data but the right table does not, the data that needs to be newly inserted into the backup database.

Now we need a branch, that is, new data needs to be inserted into the backup database, and the existing data needs to be updated to the new value. Drag a Conditional Split from the toolbar to perform such branch processing. We point the output in the Merge Sort to the Conditional Split and double-click the Conditional Split, as shown in the following figure (note that one condition is ISNULL and the other is non-ISNULL).

 

At this time, their input values are divided into two conditional outputs. Finally, we drag a OLE DB Destination to insert data and a OLE DB Command to update the database. The final process is shown in the following figure:

 

 

double-click the setting OLE DB Destionation and select the table in the destination database for data import. Note that you need to check the Keep identity option, because I used the auto-increment attribute for the ID field when creating the table.

Double-click the Settings OLE DB Command. First, select the link object in the Connection Managers Tab, and then set your Component Properties Properties in the SqlCommand Tab. As shown below:

 

what are the parameter values used? Replace the number, and then set the Column to replace the value in the Column Mappings Tab, as shown in the following figure:

 

at this point, the task is created without writing any code. 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, modify the original data table and run the preceding task to see the changes in the target database.

 

So how to finish the task regularly? You can use SQL Server Agent to call the preceding package, or use DTExec.exe to execute the preceding task in Windows scheduled task.

 

 

 

SQL database C++ data format XML Windows
create by using ssh use data synchronization tasks ssis creation synchronize data by using ssh windows development file data
developer Community> development and O & M > article
Selected, One-Stop Store for Enterprise Applications
Support various scenarios to meet companies' needs at different stages of development

Start Building Today with a Free Trial to 50+ Products

Learn and experience the power of Alibaba Cloud.

Sign Up Now