SSSIs: Use multicast Task in SSIS to write data source data to multiple target tables at the same time

Use the multicast Task in SSIS to write data source data to multiple target tables at the same time and write audit and incremental processing information

In the SSIS data flow, there is a multicast component that works in contrast to merge data flow components such as merge, merge Join or Union all, and is intuitive enough to separate a data stream into multiple data streams for downstream The Data flow component is used.

First, describe several scenarios where you use the multicast Task-

First, take part of the data from the same data source directly into a table, some of the data directly into table B. I've encountered a file with more than 370 columns, and these 370-column files can be divided into 7, 8 tables. The first thing I did was Load the data from this file into a large table, then from the large table to extract different columns to the small table, which is equivalent to the formation of two load, inefficient, and later use multicast can be a one-time direct 370 column wide file scattered to different tables.

The second, from data source A extract data to b,b each time will first Truncate, but also need to back up every time from a data extracted, this time also can use multicast. Each time the data is extracted from A, the data is directed to B's backup table by multicast.

The third, similar to the second, is that there is no backup table, but you need to keep some of the loaded Audit information data. For example, when you extract data from Source to Staging, you also need to record the number of rows taken, as well as the maximum timestamp, table name, and column name used to mark the batch of Staging data. In this case, the next time you load the data to Staging, you will only have to load the new Source data, which is the data after the previous maximum timestamp.

Of course, the same problem may have different ways to solve, you are welcome to add!

The following example shows the first and third scenarios.

First-Scatter the same Source table data into multiple target tables

To extract some of the tested data from the TSQL2012 (TSQL2012 is a sample database in the book Microsoft SQL Server high-performance T-SQL Using Window Function), our The Source table is Salesordersource.

Use biwork_ssis
go
    
IF object_id (' dbo. Salesordersource ', ' U ') is not NULL
DROP TABLE dbo. Salesordersource
Go
    
SELECT * into
dbo. Salesordersource from
TSQL2012. Sales.orders
WHERE OrderDate < ' 2006-08-01 '
    
SELECT * FROM dbo. Salesordersource