SSIS Union All output datatype issue

  • I'm having a package with a dataflow which query SQL, populate some default values along those queried and then stored them.

    -----------SQL Fetch

    Look up Table - Populate Default

    ------------Union All

    --------Store value in SQL

    In the Union All component, there's always a red X saying "[Union All [450]] 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.

  • I had this happen to me before when I mucked around with the columns in one of the buffers - either earlier in the flow or possibly even in the Union All transform itself.

    I can't remember if I was able to rectify by deleting the input column (in your case "columnname" (460)) and recreating/mapping to an output or if I had to blow away the Union All transform and then recreating/mapping it. The point of both exercises is to refresh the metadata.

    Essentially the mapping between two columns in a Union All Transform requires that the metadata of the columns match and your metadata for you input does not match your output for column (460) or at least thats what the error is stating to me.

    Good luck!

  • Hi sdevanny

    I'm in the middle of deleting the Union All component and recreating it.

    So far it seems to solves the problem. Still I'm doing more verification to make sure I didn't break anything else.

    Yup the metadata somehow got scrambled.

    Still don't know why.

    Thank you!

  • Yes it did the trick!

    Thank you sdevanny.

  • Glad it worked!

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply