DSXchange

Scenario is as follows:

Old Access DB schema
COL_NAME DATA_TYPE
'HAIR' Yes/No
'FACE' Yes/No
'LIP' Yes/No
'ORAL' Yes/No
'ID' (PK) Text

New database design schema:
COL_NAME DATA_TYPE
'ID' VarChar (PK)
'PRODUCT_TYPE' VarChar (PK)

Question:
How can I check the 'Yes' columns from the old schema and insert each as a separate record into the new table?

A simple way is to have one input and four outputs. Your question wasn't completely clear but here is a guess. You want to turn the following:

HAIR FACE LIP ORAL ID
YES NO NO YES PLEASE 12343
YES YES YES NO 435

Into this:
12343 HAIR
12343 ORAL
435 HAIR
435 FACE
435 LIP

You do this by having four outputs from your transformer, one for each product type. You then add a constraint for each output which checks the yes/no value of a particular product type, for example "InputColumn.HAIR = 'Yes'" on the first one and "InputColumn.FACE = 'Yes'" on the second one. This gives you from 0 to 4 rows outputted per input row depending on how many Yes values are found. Each output inserts into the destination table.

Vincent McBurney
Data Integration Services
www.intramatix.com

Problem solved!

Vincent: Thanks for the advice!

[:D]