Use the condition splitting Control

During ETL data extraction, data is often split by conditions. For example, when [salary], 5000, data is imported into the data warehouse; otherwise, the data is output to excel. Similar requirements. Today, I will explain how to use the condition splitting control. The goal is to output [orderstatuscode] to a data warehouse equal to 99, and output not equal to 99 to excel.

Step 1: prepare raw data, create a table, and add data to the table. The script is as follows:

Create Table [DBO]. [memberorderstatus] ([orderstatuscode] [varchar] (2) null, [orderstatusname] [nvarchar] (20) null, [Status] [varchar] (1) null) on [primary] insert into [memberorderstatus] ([orderstatuscode], [orderstatusname], [Status]) values (n'01', n'uncommitted ', n'a ') insert into [memberorderstatus] ([orderstatuscode], [orderstatusname], [Status]) values (n'02 ', n' submitted', n'a ') insert into [memberorderstatus] ([orderstatuscode], [orderstatusname], [Status]) values (N '03', N 'reviewed ', N 'A ') insert into [memberorderstatus] ([orderstatuscode], [orderstatusname], [Status]) values (n'04 ', n'sorting completed', n'a ') insert into [memberorderstatus] ([orderstatuscode], [orderstatusname], [Status]) values (N '05 ', n' in delivery ', N 'A ') insert into [memberorderstatus] ([orderstatuscode], [orderstatusname], [Status]) values (n'06', n'accept ', n'a ') insert into [memberorderstatus] ([orderstatuscode], [orderstatusname], [Status]) values (n'07 ', n' in return', n'a ') insert into [memberorderstatus] ([orderstatuscode], [orderstatusname], [Status]) values (N '08 ', N 'Return warehouse receiving', N 'A ') insert into [memberorderstatus] ([orderstatuscode], [orderstatusname], [Status]) values (N '09', N 'delivery exception', N 'A ') insert into [memberorderstatus] ([orderstatuscode], [orderstatusname], [Status]) values (N '10', N 'stockout', N 'A ') insert into [memberorderstatus] ([orderstatuscode], [orderstatusname], [Status]) values (n'11', n'order pending ', n'a ') insert into [memberorderstatus] ([orderstatuscode], [orderstatusname], [Status]) values (N '12', N 'scheduled', N 'A ') insert into [memberorderstatus] ([orderstatuscode], [orderstatusname], [Status]) values (n'13', n'generated sorting scheduler ', n' ') insert into [memberorderstatus] ([orderstatuscode], [orderstatusname], [Status]) values (N '14', N 'printed ', N 'A ') insert into [memberorderstatus] ([orderstatuscode], [orderstatusname], [Status]) values (N '15', n' generated warehouse picking ticket ', N 'A ') insert into [memberorderstatus] ([orderstatuscode], [orderstatusname], [Status]) values (N '16', N 'warehouse output', N 'A ') insert into [memberorderstatus] ([orderstatuscode], [orderstatusname], [Status]) values (N '17', N 'reviewed ', N 'A ') insert into [memberorderstatus] ([orderstatuscode], [orderstatusname], [Status]) values (N '18', n 'from return in, N 'A ') insert into [memberorderstatus] ([orderstatuscode], [orderstatusname], [Status]) values (n'19', n'auto return warehouse receiving ', n'a ') insert into [memberorderstatus] ([orderstatuscode], [orderstatusname], [Status]) values (N '20', N 'returned warehouse receiving ', N 'A ') insert into [memberorderstatus] ([orderstatuscode], [orderstatusname], [Status]) values (N '21', N 'locked ', N 'A ') insert into [memberorderstatus] ([orderstatuscode], [orderstatusname], [Status]) values (N '99', N 'cancel', N 'A ')

Step 2: Create an Excel file for storing data not equal to 99 in [orderstatuscode.

Step 3: Open Vs and create an SSIS project.

Step 4: In the control flow view, drag a Data Flow task, double-click the data flow task, enter the data flow task, drag an ole db source in the interface, and double-click the control to set the data source,:

Step 5: drag a condition splitting control and double-click it to set it. This step is the key. You need to find the data with the value of [orderstatuscode] equal to 99 and import it to the data warehouse, data not equal to 99 is imported to excel. The key to implementation is the use of functions. If you are not familiar with the functions, you can learn from the msdn official website. :

Step 6: There are two steps for the data. One is to import the data warehouse and the other is to import the data to excel. Therefore, you need to drag the two target controls. Of course, it is necessary to convert data before import. The field types of the data source and target must match. :

Step 7: run the package directly. Result: