Ok
I've built out a pretty compilcated data flow, and all is well... .occasionally I need to change a length of a data column, and everything but unionall component seems to handle the update just fine. IT says meta data doesnt match, and rather than deleting the component readd new union all component and have to wire it all back up, I wondered if there was a trick to this?

No - you don't have to delete the Union All component.

There is a problem with the Union All component propagating metadata changes - but it appears to me to be a design decision. I'm not sure the SSIS team had many alternatives - but here's hoping they'll figure one out.

The problem is that with a Union All component, the output's metadata gets defined when you attach the "first" input. Regardless of how many other inputs you attach, you're not going to have an already-defined output column's metadata changed (automatically).

There is a loophole there, however, and you can see a glimmer of it from inside the Union All editor. Float your mouse over the output column name for any "column" (shown as rows in the Union All editor). You'll see a tooltip showing you the output column's metadata. Float over any of the input column names for any "column", and you'll see that input's metadata.

When you change one (or more) of the upstream flows' metadata for a column, the tooltip in the Union All changes for all the input sources - but doesn't change for the output. To manually handle this, right-click on the row in the editor and Delete the row (actually a column - but I'm hoping you're following me here). Then, scroll down to the bottom of the list, pull the dropdown for "input 1" on your empty row, and pick the same column you just deleted. You'll now be able to see correct metadata on the output column associated with this "new" column on the output. You'll just have to pick all the other input columns to match up to it.

The only way (with five minutes thought) I can think of to "fix" this problem is to address the 95% scenario where all of your inputs' metadata agrees, but doesn't match the output's metadata. (By this, I mean you may have three inputs to the Union All, all of whom define the column in question the same - but don't match what the Union All's output is.) In that case, it would be nice if the Union All automatically changed the metadata of its output column to match what all the input sources agree on.

I think I'll submit a Connect feedback issue for that...

EDIT: There already is one there - GO VOTE FOR IT, as Microsoft has it labeled "Closed (by design)" which is NOT good enough!

---

ronneyek42,
I think that is bug hopefully it will be resolved in sp3(latest available but not sure)
You have to delete the unionall and then drag a new one but make a copy of original one to be safe
so if something goes wrong you have already a copy.

thanks

---

D

Without seeing the data flow task, is it possible to use a variable to handle the dynamically set property and possibley use a script transformation to set it? An easier process would be including a configuration file, but this is considering the specific meta-data can be configured in the task.

If you could possibly add a bit more detail as to the meta-data in the task and an example.

Hope this helps.

---

David Dye

ronneyek42,
I think that is bug hopefully it will be resolved in sp3(latest available but not sure)
You have to delete the unionall and then drag a new one but make a copy of original one to be safe
so if something goes wrong you have already a copy.

thanks

---

D

No - you don't have to delete the Union All component.

There is a problem with the Union All component propagating metadata changes - but it appears to me to be a design decision. I'm not sure the SSIS team had many alternatives - but here's hoping they'll figure one out.

The problem is that with a Union All component, the output's metadata gets defined when you attach the "first" input. Regardless of how many other inputs you attach, you're not going to have an already-defined output column's metadata changed (automatically).

There is a loophole there, however, and you can see a glimmer of it from inside the Union All editor. Float your mouse over the output column name for any "column" (shown as rows in the Union All editor). You'll see a tooltip showing you the output column's metadata. Float over any of the input column names for any "column", and you'll see that input's metadata.

When you change one (or more) of the upstream flows' metadata for a column, the tooltip in the Union All changes for all the input sources - but doesn't change for the output. To manually handle this, right-click on the row in the editor and Delete the row (actually a column - but I'm hoping you're following me here). Then, scroll down to the bottom of the list, pull the dropdown for "input 1" on your empty row, and pick the same column you just deleted. You'll now be able to see correct metadata on the output column associated with this "new" column on the output. You'll just have to pick all the other input columns to match up to it.

The only way (with five minutes thought) I can think of to "fix" this problem is to address the 95% scenario where all of your inputs' metadata agrees, but doesn't match the output's metadata. (By this, I mean you may have three inputs to the Union All, all of whom define the column in question the same - but don't match what the Union All's output is.) In that case, it would be nice if the Union All automatically changed the metadata of its output column to match what all the input sources agree on.

I think I'll submit a Connect feedback issue for that...

EDIT: There already is one there - GO VOTE FOR IT, as Microsoft has it labeled "Closed (by design)" which is NOT good enough!

---

Tried the link Todd supplied and it came up as page coud not be displayed, not sure what happend as the link appears correct.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=311149

After reading the post in Microsoft Connect this is, and has been reported as a "bug". The original post was shown as "Closed by design" and the current post also shows closed, but has a response that time ran out in Katmai, but it will be kept in mind for the next release.

---

David Dye

Yes david, few weeks ago i has the same problem i went through and found that i had to delete that and recreate that again.

---

D