SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS Union All output datatype issue


SSIS Union All output datatype issue

Author
Message
Megistal
Megistal
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2924 Visits: 2555
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.
sdevanny
sdevanny
SSC-Enthusiastic
SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)

Group: General Forum Members
Points: 123 Visits: 343
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!
Megistal
Megistal
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2924 Visits: 2555
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!
Megistal
Megistal
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2924 Visits: 2555
Yes it did the trick!

Thank you sdevanny.
sdevanny
sdevanny
SSC-Enthusiastic
SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)

Group: General Forum Members
Points: 123 Visits: 343
Glad it worked!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search