The DB Staging component loads the incoming data streams into a staging area. The data is buffered until the incoming data stream has finished, Then an outgoing data stream, which is the result set of a given SELECT statement, is created. The staging tables can be created based on the output port structure of the preceding component. Although many transformation components are designed to work on a record-by-record basis, the staging component works in two phases:
Phase 1: Collect ALL records from the preceding components.
Phase 2: Run the query and provide the records of the result set in blocks of given size.
Use this component to stage your transformation data in a dedicated area to perform aggregation or joins on data from heterogeneous sources.
You can use staging components to perform sorts or aggregations on formerly unordered records by using ORDER BY or GROUP BY clauses in the Query property.
You could also use this component for creating an intermediate image of the transformation for further inspection or processing.
Add the component to the project and connect the ports to adjacent components.
To add input streams to the DB Staging component, you can drop connections from the data providing component on the staging component. The ports are automatically created by the component.
Enter the Connection Parameters as described in “Entering database connection parameters”.
Create the staging tables. If the staging tables you are going to use already exist, go to step 6.
Right-click the component and select either Create Staging Table from Input or Create Staging Table from Port. The commands let you create the staging tables optionally based on the IN-port structure of the component or the structure of any other port within the project. However, you can also create the staging tables manually by using third party tools.
Staging options are defined by using the Stage Options window. This window also lets you define the Truncate Table and the Write Block Size for each staging table.
Open the Query window by clicking Query button, and enter the query that selects the data from the staging area.
To use the staging component