Vision Binary

SSIS : Union All Transformation Error

—Metadata for input columns does not match the metadata for output

Our Client has an SSIS package. I’ve been working on that one for monthly data migration twice. It has been working well until today.

This morning I got the new requirement– to load the Oct. datasets from delimited and  fixed width Flat File, CSV, EXCEL, DIF.

After reset all the connection manager, I opened each source file to update the dataset. For Oct. we have 46 source File so they are 46 inputs to Merge by using Union all.

There is a column is called “ExtCost”

I got the following error:

Error at Import mat [Union All [330]]: The metadata for “input column “ExtCost” (8979)” does not match the metadata for the associated output column.

Error at Import Mat [Union All [330]]: Failed to set property “OutputColumnLineageID” on “input column “ExtCost” (8979)”.

Well, we are not able to change the metadata, so to solve this kind of issue, If you would like to dynamically create SSIS packages, then leave the input columns in the source component as they are, and insert a Derived Column transformation (right after the source) with a SUBSTRING  function to limit the length of the new column.

But If your situation is that—you have already have the derived column transformation and data conversion transformation, the union all is after them. You can check all the path by right click it, see “metadata” , check those metadata which are  before the union all and after the resource files. Copy them on the clipboard , compare the metadata in the data flow path.

Because we are not able to change the metadata data, we can only change our input and output. As we have the derived and conversion transformation, we edit the data conversion transformation for those has input and output metadata unmatch. (From what you paste on your clipboard, you can find them). Then edit union all transformation, to remapping again.

Then the Union All should be no errors any more. You can continue to do the later transformation for the merged dataset.