Component: Collect and Assemble
I have received a question regarding my latest post, [Collect] How to merge tables. In that post, we used the property “Target Delete Where Clause” to delete the table data based on the data source, but what would happen if we do not have this information in the source? How can we delete the Target data if we do not have any field where we can filter by?
There is an additional feature in Assemble where we can define new fields.
Navigate to > Assemble > Packages > TC
If you see the Edit button disabled and there is not Add button, this is because the Table has been already created (you can go to the vertical view and click on “Target Table Drop”).
If the Target has not been created yet, you will see the Add and Edit buttons enabled:
Here I have added a new column “Source”. Our aim is to add a default value into this column to be able to determine in the Target table that the data is coming from this particular file.
To archive this, once we have created this new column, we need to go to Map:
DSP automatically map source columns to target columns with the same name but we can either change it or create new mappings. In this example, I am defaulting the value “Foreign_Supplier_Source” into the column “Source”:
If we execute the package we can see the 4 columns with the corresponding data:
If we go back to my previous post, we can now answer the question, how can I filter by TableName=1, 2 or 3 if my source does not have that kind of information?
This a snapshot of the filter applied:
Using the above logic, to resolve this question, we should have created a new column (in this example = “TableName”) and created three different mappings (to default to either 1,2 or 3) depending on the Source table.
I hope you have found this post interesting.
Please do not hesitate to ask any questions.
Please sign in to leave a comment.