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.
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.
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.
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.
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.