Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
In this task, you add the Derive Column Transform to the data flow. You add two derived columns, ImportType and BatchTag, to the records passed to this transform. You should add these columns before uploading the data to staging tables in MDS. These two are required columns for the staging tables in MDS. See Leaf Member Staging Tables for more details.
Drag-drop Derived Column transform from Common section in the SSIS Toolbox to the Data Flow tab.
Right-click Derived Column Transform in the Data Flow tab, and click Rename. Type Add Columns Required by MDS and press ENTER.
Connect Filter Duplicates to Add Columns Required by MDS using the blue connector. You should see the Input Output Selection dialog box.
In the Input Output Selection dialog box, select Unique Records, and click OK.
.jpg "Input Output Selection Dialog Box")
Click SSIS on the menu bar and click Variables.
In the Variables window, click Add Variable button on the toolbar.
.jpg "SSIS Variables Window")
Type ImportType for the Name and 2 for the value. You specify the value as 2 because you want to add new members to an entity in MDS. For details about this parameter, see Leaf Member Staging Table.
Click Add Variable toolbar button again.
Type BatchTag for the Name, select String for the Data type, and EIMBatch for the Value. BatchTag is just a unique name for the batch you will be submitting to MDS.
In the Data Flow tab, double-click Add Columns Required by MDS.
In the Derived Column Transformation Editor dialog box, in the list box in the bottom pane, type ImportType for the Derived Column Name.
Expand Variables and Parameters in the top-left pane, drag-drop User::ImportType to the Expression column.
.jpg "Derived Column Transformation Editor")
Type BatchTag in the next row for the Derived Column Name.
Drag-drop User::BatchTag from Variables and Parameters to the Expression column.
Click OK to close the Derived Column Transformation dialog box.
Next Step
Task 13: Adding OLE DB Destination to Write Data to MDS Staging Table