vrijdag 10 september 2010

Lookup join - Partial cache - Package Validation Error

In a former post i blogged about the full cache and i was figuring out how the partial cache is working. So i changed the full cache mode to partial cache and immediately i ran into an error.



and more specific:

TITLE: Package Validation Error

------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at DFT_AdventureWorks [Lookup [411]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error".
Error at DFT_AdventureWorks [Lookup [411]]: OLE DB error occurred while loading column metadata. Check SQLCommand and SqlCommandParam properties.
Error at DFT_AdventureWorks [SSIS.Pipeline]: "component "Lookup" (411)" failed validation and returned validation status "VS_ISBROKEN".
Error at DFT_AdventureWorks [SSIS.Pipeline]: One or more component failed validation.
Error at DFT_AdventureWorks: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)

I was trying to figure out what was going on and i found out i has something to do with this query in the lookup transform:

--16989 out of 19820
SELECT CustomerID, AccountNumber
FROM Sales.Customer
WHERE CustomerID % 7 <>0;

I've found out there are a couple things wrong with this query:
  • --16989 out of 19820 is not allowed in the query window.
  • ; is also not allowed in this window.
What i also found is that when you press OK immediately after you corrected the error, the error dot will stay there!! But when you go to the Columns tab and when you press ok after this, the error disappears(??). Bit awkward and this cost me a lot of frustration before i found out about this.

Greetz,
Hennie

1 opmerking:

  1. Thank you for your post. It is really surprising and awkward error. I mean to say look up query allows ; in all the modes Partial, Full and no cache.But at run time it throws exception for no and partial cache. Thank again.

    BeantwoordenVerwijderen