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


How to reset metadata in SSIS packages


How to reset metadata in SSIS packages

Author
Message
Lane Tharp-342944
Lane Tharp-342944
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 21
When working with SSIS and source or destination metadata changes SSIS does not reset the metadata it deletes the changed column and adds a new one at the end of the list. This results in messy packages after several changes have occured.

To reset the metadata on data sources, change the source to a table or query that has no matching column names and then view columns in the data source. This will delete and recreate the metadata. Then swith the source back to the changed source you are trying to refresh metadata for and view columns again. This will completely recreate the metadata for the source.

For destinations, change the destination to one with no matching column names and then view mapped columns. This will delete and recreate the metadata. Then switch back to the new or changed destination and view mapped columns. This effectively resets the metadata.

Hope this is helpful.
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27575 Visits: 13268
When I read your recommandation for refreshing the metadata of the data source, I get the feeling that in the case of an OLE DB Source, you select the table from the dropdown box. This is a big no-no. (see here. Point 4 to be precise). Actually, selecting a table from the dropdown is even worse than select *.

Always use a SQL statement to select the data from your source. However, this will not prevent SSIS to screw up the metadata. To refresh the source in this case, replace the select statement with select a = 1. Then, after the metadata is refreshed, paste back your original statement.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Lane Tharp-342944
Lane Tharp-342944
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 21
There really is no such thing as a big no no if you know what you are doing.

I disagree with your suggestion. When you select a table from the list your SQL is retained. When you switch back to an SQL Statement your SQL is still there. When you recreate the columns, you have refreshed the metadata in 4 clicks with no hassles.

Select * is not a no no if you want all columns in all rows. This post is about refreshing metadata. Select the table if you want the whole thing. Sometimes that is the case.
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27575 Visits: 13268
Using select * could cause SSIS to crash if a column is added to the table (or removed). That's why you want to refresh the metadata, to stop SSIS from crashing.

Using a SQL statement will not cause SSIS to crash, unless you changed datatypes. My reply was just an extension of your post to illustrate how one should refresh the metadata if a SQL statement is used.

Saying select * is OK because you need all columns is a valid point, but you cannot know now if at some point in the future a column is added that you don't need. For example a creation_datetime or a delete_indicator column.

Finally, as I said in my previous post, there is a difference under the hood between selecting the table from the dropdown and using select *. See here for more info. On most lists of SSIS best practices, you will find an item that advices you against using the dropdown in OLE DB sources.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Lane Tharp-342944
Lane Tharp-342944
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 21
Dont see how selecting a table from the drop down for 2 seconds to refresh metadata is such an issue. But I do see you driving traffic to SSISJunkies.

Think you may be a little confused and maybe on the wrong subject or post for what you are trying to share.

Perhaps you could start your own post for select * and stop cluttering mine about metadata refresh to promote another site.

Moderators, can you clean this up? We have gotten off topic and started talking about something completely different than the topic suggests.
Bruce Hendry
Bruce Hendry
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 259
So in SSIS 2012 (VS2010 using SSDT) I am noticing an issue with metadata refresh that no longer responds to the tricks described in this post, and worse still, the metadata retains uncommon columns, as well. I am curious if anyone else is seeing this, and more curious if there is a workaround. It renders leveraging the copy & pasting of source and destination components fairly useless.

Example:
Package A loads Customer data with OLE DB Source and OLE DB Destination. Package is copied and pasted, guid's reset, renamed to Package B.

OLE DB Source is updated to point to Account table. Refresh metadata via pointing to a table/statement with no common columns still seems to work for source, columns are reset and ordered as expected.

Destination updated but still retains ALL columns from Customer table, regardless of pointing to new destination with no common columns and then pointing back. I have tried a bunch of different fake outs; only thing that works is dropping the OLE DB Destination and adding a new one.

Anyone else noticed this behavior?
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27575 Visits: 13268
Bruce Hendry (3/29/2013)
So in SSIS 2012 (VS2010 using SSDT) I am noticing an issue with metadata refresh that no longer responds to the tricks described in this post, and worse still, the metadata retains uncommon columns, as well. I am curious if anyone else is seeing this, and more curious if there is a workaround. It renders leveraging the copy & pasting of source and destination components fairly useless.

Example:
Package A loads Customer data with OLE DB Source and OLE DB Destination. Package is copied and pasted, guid's reset, renamed to Package B.

OLE DB Source is updated to point to Account table. Refresh metadata via pointing to a table/statement with no common columns still seems to work for source, columns are reset and ordered as expected.

Destination updated but still retains ALL columns from Customer table, regardless of pointing to new destination with no common columns and then pointing back. I have tried a bunch of different fake outs; only thing that works is dropping the OLE DB Destination and adding a new one.

Anyone else noticed this behavior?


This change is by design. They redesigned SSIS in SQL Server 2012 and the way it handles metadata in the dataflow. It now "remembers" metadata, so you can build your dataflow from destination to source, instead of the usual way around. So that's the problem you are seeing: it remembers columns it shouldn't remember because the source has changed. Make sure your dataflow is fully connnected from source to destination, double click on an arrow and map the columns. There should be a checkbox about dropping unmatched columns or something like that.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Bruce Hendry
Bruce Hendry
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 259
Koen Verbeeck (3/30/2013)

This change is by design. They redesigned SSIS in SQL Server 2012 and the way it handles metadata in the dataflow. It now "remembers" metadata, so you can build your dataflow from destination to source, instead of the usual way around. So that's the problem you are seeing: it remembers columns it shouldn't remember because the source has changed. Make sure your dataflow is fully connnected from source to destination, double click on an arrow and map the columns. There should be a checkbox about dropping unmatched columns or something like that.


Thanks Koen. You are correct, an "unmapped columns" dialog comes up when double clicking an arrow connector in the scenario I described. The dialog contains an option to remove the unmapped columns and it works like a champ. It still leaves "common" columns sorted at the top and it's necessary to use the trick of pointing to a source with no common columns to completely eradicate and re-sort.

Thanks for the help.
MtnJim
MtnJim
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 73
I have been experiencing the same issues as stated above. It may be easy to reset and remap but this is a costly process when you have very wide tables with multiple data conversions. I had a mapping where I changed the datatype on a string to dt_dbdate. The data type in the destination was datetime so I changed it to date to match. When I try to run the package I receive errors that it won't convert to a DT_WSTR?

I tried the solution above to reset everything but am still seeing the issue. My resolution was to use the OLE DB destination instead of SQL Server destination and it will now allow for the change I made in my destination.
David Ziffer
David Ziffer
SSC Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
Points: 243 Visits: 203
SSIS's refusal to relearn metadata from its data sources and destinations is a constant, major problem that has been plaguing SSIS for over a decade now, no doubt harking back to the days when someone at Microsoft wrote it as a quickie one-off for his own personal use (I suspect), never imagining that it would one day be used in production environments. I just don't see why Microsoft can't add a "reset metadata" button and end this whole insanity. How many thousands of hours are wasted every year by SSIS users over this?
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