dinsdag 12 juli 2011

SSIS VS_NEEDNEWMETADATA strange message

At one of my current customers I created a simple package to export views to Excel documents.
Because I'm a bit lasy I copied the first package I did create to generate a new one for the next view.

The package contains the following DFT:

When I do run the package the following error is generated:

It's not really clear what the error is so let's take a look at the sysssislog:
The external columns for component "OLE_SRC OLE_DB_ZH308 ZH308GBSturing ZH308 varSQLSelectSource_str" (85) are out of synchronization with the data source columns. The column "ZH308_DBC_ID_Trigger" needs to be added to the external columns.  The column "Eerste_Trigger_Datum" needs to be added to the external columns.  The column "Laatste_Trigger_Datum" needs to be added to the external columns.  The column "ZH308_Aangemaakt" needs to be added to the external columns.  The column "Historische_Journaalposten_Aanwezig" needs to be added to the external columns.  The column "DBCTraject_Gegegevens_Aanwezig" needs to be added to the external columns.  The column "DBCFactuur_Gegegevens_Aanwezig" needs to be added to the external columns.  The column "DBCPatient_Gegegevens_Aanwezig" needs to be added to the external columns.  The column "DBCUZOVI_Gegegevens_Aanwezig" needs to be added to the external columns.  The column "UZOVI_Digitaal_Factureren_Mogelijk" needs to be added to the external columns.  The column "DBCSpecialisme_Gegegevens_Aanwezig" needs to be added to the external columns.  The external column "Bedrag" (333) needs to be removed from the external columns.  The external column "CodeVerzekeraar" (330) needs to be removed from the external columns.  The external column "Factuurnummer" (327) needs to be removed from the external columns.  The external column "Titel" (324) needs to be removed from the external columns. 

Seems to be clear, or not? When you looked at my DFT task you did maby see that I don't have the OLE_SRC OLE_DB_ZH308 ZH308GBSturing ZH308 varSQLSelectSource_str source in there.
All MetaData of the OLE_SRC, is what I would expect.
So do open the advanced editor for the OLE_SRC OLE_DB_ZH308 :

Thats strange, all messages saying the column needs to be added or column needs to be removed, why I do not see it here. Do you?

It seems to be there somewhere, so to be sure I did take a look at the XML code of the package and did found:

What is that doing there? Why is it none gone! From the previous error from sysssislog whe can see the error is generated from the component id 85

As you can see in the lower part of the image above, my current OLE_SRC is has component id 201. The shown component id 85 is not even visable in the DFT any more.

How to solve this? I tried to remove the component id 85 from the XML code but this will end-up with a corrupted package. Need to find out what to do next time. So my final but not real great solution was creating the package from scratch. I needed to do this because I did rename my OLE_SRC task already. Recreating a OLE_SRC task with the same name and removing it again does not solve the issue.

The best way is just prevent this issue! When you have duplicated your template package the first you do is remove the OLE_SRC task and add a new one.
You can replay this issue in the following way:
1 - Create a DFT
2 - Add a SQL OLE_DB source with setting the SQL Data access command to : SQL Command and get your query in.
3 - Now copy your package to generate a new one
4 - Change the SQL Data Access command for the SQL OLE_DB source to : SQL Command from variable
5 -  Save your package and look at the XML code, when fine you still see the SQL Command setting as component in your package...

Hope you enjoy this blog! When you have comments, better solution and recommendation I like to hear from you. Dynamic Intelligence | LinkedIn | Blog | Twitter

Geen opmerkingen: