@neeraj aitha Here's how to merge 3 streams into 1 using the conditional select method with Derived Column in ADF Dataflows:
Select Desired Columns:
- For each of Streams 1 and 2, use the Select transformation to pick only the specific columns you want to include in the merged stream.
Create New Columns with Null Values:
- For Streams 1 and 2, utilize the Derived Column transformation to generate new columns that match the remaining columns from Stream 3.
- Within Derived Column:
- Define new column names corresponding to the missing columns in Streams 1 and 2.
- Set the expression for each new column to a constant value (e.g.,
null
or a default value) to maintain consistency.
Example (assuming Stream 1 has 1 column and Stream 2 has 2 columns):
- Stream 1 has a column named "source1_data". You want to include it in the merged stream.
- Stream 2 has columns "source2_col1" and "source2_col2". You want both in the merged stream.
- Stream 3 has 54 columns, including "col1", "col2", ..., "col54".
Stream 1 Transformation:
- Select: Choose "source1_data"
- Derived Column: Create 52 new columns (col1 to col52) with an expression like
null
or a specific default value.
Stream 2 Transformation:
- Select: Choose "source2_col1" and "source2_col2"
- Derived Column: Create 50 new columns (col3 to col52) with an expression like
null
or a specific default value.
Merging Streams:
After applying Select and Derived Column to Streams 1 and 2, use the Union transformation to combine all three streams into a single one.
- Union requires all input streams to have the same number of columns (even if some contain null values).
Continuing the Flow:
Once you have the merged stream, you can connect it to subsequent transformations in your data flow for further processing.