Shulou(Shulou.com)05/31 Report--

This article is to share with you about how to create synchronous database data tasks in SSIS. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article.

These packages can be used independently or in conjunction with other packages to meet complex business needs. 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 that data into one or more targets. (excerpt from MSDN, see http://technet.microsoft.com/zh-cn/library/ms141026(v=sql.105).aspx for more details.) below I use SSIS to demonstrate a real-world example. For example, I have a database, for backup data or other purposes, the data from this database will be migrated to other databases on a regular basis. During migration, some new fields will be inserted into the backup database, and some modified fields will also be modified in the backup database. Now let's use SSIS to accomplish this task. First, run the following SQL in my source database db_source and target database db_destination to create the required tables, and demonstrate with this table test_1. The copy code is as follows: CREATE TABLE [dbo]. [test_1] ([Id] [int] IDENTITY (1) NOT NULL primary key, [Name] [varchar] (50) NULL, [Age] [int] NULL)

Build a table and add a few records to the source table at will, leaving the target database empty for the time being.

Now let's open VS and create an Intergration Services Project. (note: if SQL Server is installed in Express, there is no project template for this project.)

After creating the project, drag a Data Flow Task under the Tab of Control Flow, as shown below:

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

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

After dragging out the data source, double-click it, you can make some settings for it, mainly to link the database and select the table or view you want to migrate and other settings, I will not elaborate here. Note that, as shown above, if a red X appears on a graph, there is an error in the setting.

Then drag two Sort and one Merge Join, point the previous data source arrow to the two Sort, and enter the data from the last two Sort into the Merge Join at the same time.

Double-click the two Sort, check the ID in the table, and sort the ID field once. Because the Merge Join process requires the input data to be sorted. This sort can also be sorted by setting the SortKeyPosition property on their output fields directly in the data source. (see http://msdn.microsoft.com/zh-cn/library/ms137653.aspx for details)

Here, 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 diagram, the left input is the left input, and the right input is the right input. Then we double-click Merge Join and set it as shown in the following figure:

What is checked here is the output data after this process. Join Type needs to select Left outer join, because on the left is our original data table, and on the right is the table we backed up. The right table can be regarded as a subset of a left table. If there is data in the left table, but not in the right table, those are the data that need to be newly inserted into the backup database.

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

At this point, their input values are divided into two 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 to set OLE DB Destionation and select the table in the target database where the data is imported. It is important to check the Keep identity option here, because I used the self-increment attribute for the ID field when I created the table.

Double-click to set OLE DB Command, first select the linked object in the Connection Managers Tab, and then set your SqlCommand property in the Component Properties tab. As shown below:

All the parameter values here are used? And then set the column where the substitution value is actually replaced in the Tab Column Mappings, as shown below:

At this point, the task is created, no code is written, and the task is directly procrastinated. Now you can press F5 directly in VS to see the effect, and our target data table will insert the values in the source data table. Then we modify the original data table, and then run the above task, and we can see the changes in the target database.

So how to finish the task on time? Here you can use SQL Server Agent to call the package we wrote above, or use DTExec.exe in the Windows planning task to perform the above task.

The above is how to create synchronous database data tasks in SSIS. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.