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

How to reset metadata in SSIS packages Expand / Collapse
Author
Message
Posted Tuesday, May 25, 2010 10:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 26, 2011 2:19 PM
Points: 4, 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.
Post #927634
Posted Wednesday, May 26, 2010 2:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:42 PM
Points: 13,239, Visits: 11,018
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #928022
Posted Wednesday, May 26, 2010 6:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 26, 2011 2:19 PM
Points: 4, 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.
Post #928183
Posted Wednesday, May 26, 2010 7:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:42 PM
Points: 13,239, Visits: 11,018
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #928259
Posted Wednesday, May 26, 2010 8:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 26, 2011 2:19 PM
Points: 4, 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.
Post #928291
Posted Friday, March 29, 2013 2:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 1:45 PM
Points: 24, Visits: 196
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?
Post #1437096
Posted Saturday, March 30, 2013 1:45 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:42 PM
Points: 13,239, Visits: 11,018
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1437235
Posted Monday, April 1, 2013 3:52 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 1:45 PM
Points: 24, Visits: 196
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.
Post #1437643
Posted Thursday, May 9, 2013 11:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 10:40 PM
Points: 2, 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.
Post #1451271
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse