Here is a very strange situation. I have a SQL Server 2008 R2 SSIS package that has been working fine for months. Overnight it started erroring, specifically a Lookup task failed with "error converting character data to uniqueidentifer". Seems fairly simple on the face of it; there must be some non-character data being retrieved by the Lookup task. The query used by the Lookup task is:
SELECT QuoteKey, CAST(TransactionId AS uniqueidentifier) AS TransactionId
FROM dbo.Fact_Quotes Q
WITH (NOLOCK) INNER JOIN dbo.Dim_Date D WITH (NOLOCK) ON Q.DateKey = D.DateKey
WHERE D.YearMonthDay >=CONVERT(CHAR(8), DATEADD(dd, -7, CURRENT_TIMESTAMP), 112) AND Q.ServiceTypeKey = 6
Trouble is, when I ran the above query in SSMS, data was returned without any error. On a whim I changed the caching properties from 'No Cache' to 'Full Cache' and would you believe it, the Lookup task processed without error! I then changed back to 'No Cache' just to check and the error reoccured.
Why would changing the caching mode of a Lookup task cause this behaviour?
Thanks and regards