How to bypass SSIS VS_NEEDSNEWMETADATA error?

Question

I've created a SSIS package using the Import/Export utility from SQL Server to export data from a view to an Excel file. I've also scheduled it through a schedule in SQL Server and everything is working perfectly.

The view that I'm exporting data from is actually taking data from multiple views and so on, for about 3 levels in "hierarchy".

I'm encountering an error VS_NEEDSNEWMETADATA when I modify the column aliases of any of the views that go into the top level view, from which I'm exporting data.

After research I understand that this is why I'm getting the error, but is there any way to "propagate" the metadata of the "new view" so I don't have to recreate the SSIS package everytime I make a change in the structure of the view?

Answer 1

Nope. A data flow in SSIS is tightly bound to the source. A change to the column name or data type is going to cause the validation step to fail resulting in your error.

To mitigate against it, I would either look at keeping your column aliases the same - views/table valued functions/stored procedures are handy in this regard.

The other approach that I would use is to automate the package creation. Based on your profile, I suspect this would a simple thing to do. Rather than right-click in SSMS to recreate your SSIS package per change, use whatever technology you find appropriate. I've used the base .NET SSIS libraries for package creation but they're painful for me when I'm working with COM (data flow items). I then built packages using the EzAPI which abstracts away some of the COM stuff but it's not 100% feature complete. My current love for alternate package construction is Biml.

Download BIDS Helper from CodePlex. It's free and provides a lot of useful functionality that didn't make the cut for SSIS/SSRS/SSAS development. Nestled away in there is the ability to transform the Biml XML into SSIS package XML. You can look through my answers and see some of the ways it works. In your case, it's going to be a very basic OLE DB Source to an Excel Destination. You specify the source view name and any time you make a change to the view, click generate package and you're done. Biml will reach out to your source, identify the metadata and use that to generate the datatypes and names for your destination.