Adding DB Staging to a project opens a property window. Use the property window to define connection parameters, staging tables, and query values.
You can create staging tables 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.
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.
To use the staging component
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.
