Package connects to SQL Server DB. Package is retrieving data from a synonym in the DB for Lookup transformation. The same DB and synonym exist on Test and production server. When we set the connection manager to TEST, everything works fine. But when we connect to Production, we get below error when selecting Columns for Lookuptransformation Error at Inferred Values [Lookup [26733]]: 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: 0x80040E14 Description: "Deferred prepare could not be completed.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Unspecified error". Error at Inferred Values [Lookup [26733]]: OLE DB error occurred while loading column metadata. Check SQLCommand and SqlCommandParam properties. Error at Inferred Values [Lookup [26733]]: The call to Lookup transform method, ReinitializeMetadata, failed.

Is the meta data for the table same in the test & Prod env??Hope this helps !! - Sudeep | Please mark the post(s) as Answered that answers your query.

Check the Metadata (Filed names and types) from the source in your test inviroment with the one in productionSincerely SH -- Please kindly dont forget to mark the post(s) that answered your question and/or vote for the post

-Yes, the Test and Production metadata are exactly the same. -We just detached, copied and reattached the database from QA to Production.-Wehave also set some property in the package not to check metadat. Set Validate against external metadata to false but it still checks for metadata.-We needto use Synonyms and not views because of some business constraintsRunning a SELECT query from SSMS directly on the Synonym returns data without any error.Any suggestions?

OK i am just going to give you a step by step check list , and then please port the results thanksso basically you are saying that you have only chnaged the SQL server name, becaus the source tables (production or your second DB )are the same as the Test DB (your First DB)1- In CONNECTION MANAGER , check the NEW sql server name , check on test , check the DB name2-In the DFT go to your source object i assume its OLE DB SOURCE, check the table name and connection, preview it? do you see nay records and field names?3- close OLE DB SOURCE, than then Right click on the OLE DB SOURCE select "SJOW ADVANCED EDITOR" , check the properitesSincerely SH -- Please kindly dont forget to mark the post(s) that answered your question and/or vote for the post

Thanks Nik3 for the post. Here is answers to your steps/questions:1. SQLserver name and DB names looks correct2. Preview works. I do see field names and records3. Compared all the properties between these two machines, they are same.Still getting error when selecting Columns for Lookuptransformation.Running a SELECT query from SSMS directly on the Synonym returns data without any error.Any suggestions?

Any comments or suggestion from MSFT guys in this forum? Is this a UI bug?

Hi Nafij, We are working with someone in support on a similar issue as yours. (It may be your company who called us - I don't know), but if we resolve the issue, we can post the resolution here to follow up.We were able to reproduce the issue here at Microsoft, and its not something we have a canned answer for.Thanks, JasonDidn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance

Jason,Was this issue ever resolved I am running into the same exact thing after upgrading a 2005 package to 2008. I can no longer access information in synonyms and am getting the same error as described above. Thanks for any help/info you could pass along.Andy

It kinda late for this, however, if there was an interruption between the 2 servers; you must re-executes your synonyms again and it works fine!!

We are facing this same issue. Synonym works fine via SSMS but not via SSIS when the query takes more than a few seconds to complete. If the query runs quickly, the synonym works fine in SSIS. Odd. I searched Connect and couldn't find this issue. Should I file a bug on Connect? Jason, did you all ever resolve this back in August of 2009? Thanks, Kevin

The problem we worked on was isolated to "For Browse" or "SET NO_BROWSETABLE ON" queries which are used by SQL Native client providers under SSIS in places like the lookup component to retrieve metadata for the columns. More broadly, we found the scope of the problem may exist when using 1. synonyms 2. Views and 3. Service broker queues. This especially impacts SSIS and linked servers, but not necessarily limited to those. The "Unspecified error" was because an access violation (AV) occurs on the SQLServer where the SYNONYM/View resides. If that is the case, you may note .mdmp minidump files in the SQL Server engine's MSSQL/LOG/ folder if that is the same scenario. One matching connect item at that time was this one. http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=416574 A fix was made in Service Pack 2 for SQL Server 2008 which was thought to take care of this situation. I'm not sure if you have SP2 installed already (10.00.4000+ when you do SELECT @@version or look at Management Studio at the object explorer). Thanks, JasonDidn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance