How to use the multicast Task to write data to multiple target tables at the same time

Opening introduction

There is a multicast component in the SSIS data flow that works in contrast to the merged data stream components such as merge, merge Join, or Union all. Very intuitive, it can separate a stream of data into multiple streams for use by other data flow components downstream.

Several scenarios for using 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

Create two target tables, one for storing order-related information, and one for storing ship-related information simply.

IF object_id (' dbo. SalesOrder ', ' U ') is not NULL
DROP TABLE dbo. SalesOrder

IF object_id (' dbo. Ordership ', ' U ') is not NULL
DROP TABLE dbo. Ordership

CREATE TABLE dbo. SalesOrder
(
   OrderID int,
   CustID int,
   EmpID int,
   OrderDate datetime,
   createdate datetime DEFAULT (GETDATE ())

CREATE TABLE dbo. Ordership
(
   OrderID int,
   shippeddate DATETIME,
   ShipperID int,
   freight money,
   ShipName NVARCHAR (),
   createdate DATETIME DEFAULT (GETDATE ())
)

The process of creating a Connection manager is a little more, create a new data flow Task, and then create an OLE DB source to point to the Salesordersource table.

In Columns, select only the columns that need to be output down to reduce unnecessary data transfer.