I'm having a package with a dataflow which query SQL, populate some default values along those queried and then stored them.
Look up Table - Populate Default
--------Store value in SQL
In the Union All component, there's always a red X saying "[Union All ] Error: The metadata for "input column "columnName" (460)" does not match the metadata for the associated output column.". One such error per column used in the Union All component.
1- When comparing both data flow (green line) using the Metadata Data Viewer just before the Union all component, both shows correct data type for all columns (DT_NUMERIC 18, 6).
2- Using the "Show Advanced Editor" of the two previous components (look up table & populate default) shows correct input and output datatype (DT_NUMERIC 18, 6).
3- Each column in the SQL Fetch query was wrapped with CAST(col AS DECIMAL(18,6)) to make sure SSIS use the required data type (DT_NUMERIC 18, 6 which are used correctly up to the Union all component)
4- The data flow line (green line) using the metadata data viewer after the union all component scramble all data type which surely provoke the error I'm having. (DT_R8) is used instead of (DT_NUMERIC 18, 6)
5- Both SQL tables, uses the same data type (DECIMAL(18,6)).
When running the package, the control flow stop at the data flow task and color in red the union all component in it. Nothing more (no data flow component are run)
Turning false the "ValidateExternalMetadata" property of the Union all component doesn't change anything.
Closing BIDS and re-opening it doesn't do anything either.
I can't determine what's wrong and I'm clueless.
Thank you for your help on this one.