SSIS Derived Column Missing Downstream

Question

I've created a derived column that translates a 1 to an 'M' and a 2 to 'F'. i.e. a gender indicator. The derived column feeds into a Fuzzy Lookup transformation and then to a conditional split. The problem is the derived field does not show up in any of the downstream components. In the Fuzzy Lookup transform the "Pass Through" checkbox is checked for the derived column, but in the following Conditional Split transform the column does not show up at all. Funny thing is that the _Similarity_Gender_Derived does show up in the column list for the conditional split.

Hopefully someone else has seen this type of behavior.

Thanks - Mr. Do

Answer 1

Did you add the Derived Column into an already existing transform chain?

If you did then there's a good chance that one of the transforms further down the queue is set to not pass on this newly derived column. Check all the transforms below and make sure that you're derived column is set to be passed through.

Answer 2

1. Right click on the Fuzzy Lookup task and select Show Advanced Editor.
2. Go to the "Input and Output Properties" tab.
3. Expand the "Output" item, and then the "Output Columns" item.
4. Is your derived column listed there?

If it is, it should also show up on the available input columns of the Conditional Split task. If not ...

1. Right click on the Derived Column task and select Show Advanced Editor.
2. Go to the "Input and Output Properties" tab.
3. Expaned the "Derived Column Output" item, and then the "Output Columns" item, and select your derived gender column.
4. Note its LineageID attribute.
5. Repeat the earlier steps to get the Fuzzy Lookup's Output Columns.
6. Hit the "Add Column" button. Name the column the same name as your derived column, and in the "SourceInputColumnLineageID" attribute, enter the LineageID you noted earlier.

Alternate answer: is your derived column creating an all new column, or simply replacing your existing "1/2" column? In the Derived Column Editor, check your "Derived Column" .. umm .. column. If you are just replacing your existing column with the new value (instead of adding a new column) you may just be looking in the wrong place.

Answer 3

Thanks for the response. Turns out that issue had to do with some corruption with the meta data. I ended up going back into the Derived Column Transform, renamed the column in error, then added a new derived column with the old name. I saved the transform, and then removed the original column. That fixed the problem.

Thanks for the responses.