How to reset metadata in SSIS packages

  • 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.

  • 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.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • 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.

  • 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.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • 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.

  • 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?

  • 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.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • 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.

  • 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.

  • 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?

  • I can give you one solution to this, in the case where your data source is an OLEDB data source and the database entity driving this source changed its emitted data type (but the OLEDB data source is still emitting the old type). I'm doing this in SSIS 2012:

    1) Double-click the "Data Flow Path" error coming out of the OLEDB source, look at the "Metadata", and verify that the type of your column(s) is the wrong, old data type. This means that the OLEDB data source itself has failed to change its output type. Close this dialog box.

    2) Right-click on the OLEDB data source and choose the "Show Advanced Editor".

    3) Select "Input and Output Properties" tab.

    4) Under "OLE DB Source Output" look under "External Columns" (a term which confuses me eternally) and check to see that the data types of the columns already agree with what you think your query emits. If not, then you probably have some problem with your query.

    5) Then open "Output Columns", which is what the OLEDB data source actually emits. If your situation is like mine, then you'll see the former, incorrect data types in there, i.e. these types will not match the "External Columns" types for the same columns. Change the data types to the correct ones; if a new type is some sort of string, be sure to set the length as well.

    6) Close the editor with the "OK" button. At this point the data source should now be in the condition that it should have been in if SSIS had had the ability to recognize the metadata difference automatically.

    Hope this makes your day!

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply