Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS Union All output datatype issue Expand / Collapse
Author
Message
Posted Wednesday, July 10, 2013 10:11 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 5, 2014 1:48 PM
Points: 1,420, Visits: 2,087
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.
Post #1472232
Posted Wednesday, July 10, 2013 11:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 5:03 PM
Points: 32, Visits: 307
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!
Post #1472260
Posted Wednesday, July 10, 2013 11:29 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 5, 2014 1:48 PM
Points: 1,420, Visits: 2,087
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!
Post #1472262
Posted Wednesday, July 10, 2013 11:44 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 5, 2014 1:48 PM
Points: 1,420, Visits: 2,087
Yes it did the trick!

Thank you sdevanny.
Post #1472267
Posted Wednesday, July 10, 2013 11:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 5:03 PM
Points: 32, Visits: 307
Glad it worked!
Post #1472274
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse