Metadata Refresh
-
I have a package that contains several Sequence Containers. Basically they drop a worksheet in Excel, recreate it and then populate it with data from SQL Server. Each container is a separate worksheet in the Excel file. I then sends the file off to a group of users. The issue I am having is that each time I go in and add one or more containers to the system it works fine in development but when I deploy it to the server I get validation errors for some but not all of the containers. Sometimes they are new ones that were just added and sometimes not. All of the containers are the same except that they just pull information for different reps.
The validation errors I am getting are of the following nature:
The external metadata column collection is out of synchronization with the data source columns. The column " " needs to be updated in the external metadata column collection.
A month or so ago I got them and found the following link, which I used and it seemed to get me by. Now, this doesn't even work:
http://followtheheard.blogspot.com/2007/10/ssis-external-metadata-refresh.html
There are about 27 containers in the package.
Any help would be great. I'm out of ideas.
-
Well, I don't think there's any way to do a metadata refresh except by hand in the GUI.
At this point I would say you've got 2 options:
1. Script the solution so that you're building the connection from scratch each time and you should have your metadata then.
2. Don't drop the excel. Just delete the data out of it, then you can reload it. This will keep the connection with the metadata in the file and you should be fine. I've never actually tried that, but it should be possible to pull off. I imagine you'd have to turn to a script for that. Perhaps the framework has something for deleting data from excel.
I'm just trying to give you some ideas here.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
I usually set the DelayValidation property of the data flow task to "true" so the designer does not realize those metadata changes. You have to be sure that the metadata does not change otherwise you would get a runtime error.
-
yes, set the DelayValidation to true for each data flow. then create a new VB (or C#) app. Add a reference to: C:\Program Files (x86)\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll
(also you can probably change the "90" to "80" or "100" depending on the version of SQL Server you are using" I only tested this with 90)
then in a form add a button and add this code to the button:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sPackage As String = "C:\Documents and Settings\joe\My Documents\Visual Studio 2005\Projects\MyProject\MyPackage.dtsx"
Dim app As Microsoft.SqlServer.Dts.Runtime.Application = New Microsoft.SqlServer.Dts.Runtime.Application()
Dim ev As Microsoft.SqlServer.Dts.Runtime.IDTSEvents
Dim pkg As Microsoft.SqlServer.Dts.Runtime.Package = app.LoadPackage(sPackage, ev)
Dim ext As Microsoft.SqlServer.Dts.Runtime.Executable
Dim var As Microsoft.SqlServer.Dts.Runtime.Variables
Dim log As Microsoft.SqlServer.Dts.Runtime.IDTSLogging
pkg.Validate(pkg.Connections, var, ev, log)
pkg.UpdateObjects = True
For Each ext In pkg.Executables
ext.Validate(pkg.Connections, var, ev, log)
Next
app.SaveToXml(sPackage, pkg, ev)
End Sub
-
Grasshopper,
The updateobjects option in SSIS is a mystery to me. If I add a new column to a destination table, and don't refresh the package, would this option allow for the metadata to refresh at runtime? This has been a constant problem with SSIS where DTS was great at.
Thanks for your help.
-
I have not tried this with adding a new column. My problem was the column (varchar) lengths kept changing. This worked to resolve that problem.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply