March 4, 2010 at 1:19 am
Im using an OLEDB Command to call a certain procedure, which is failing at runtime with the following error: (Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error".)
Been doing some research and there's nothing wrong with the passing of the parameters nor is there any update statement in the procedure which was working just fine.. In fact the problem seems to be with the transformation itself: ([DTS.Pipeline] Error: component "OLE DB Command" (79785) failed the pre-execute phase and returned error code 0xC0202009.)
Recently however I added an sql task transformation to the flow which creates and fills a temporary table. I don't find any reason though why it should affect the flow, nevertheless its the only thing that has changed in the package ever since it was executing normally!
any ideas??
March 4, 2010 at 2:06 am
You mentioned a temporary table.
When SSIS starts evaluating your package, the temporary table doesn't exist yet.
Hence, he will find a reference to an object that he cannot find.
Solution: you must set the property DelayValidation of all components/tasks that use this temporary table to True.
See if that helps.
Regards
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 4, 2010 at 5:43 am
The delay validation property is already set to true and the temporary table is not related to the procedure in any way! I tried to disable the DFT that is failing, another DFT also containing an OLEDB Command calling the same procedure fails on pre-execution, the weird thing though is that the validate external metadata property on that transformation is set to False... im starting to doubt either it has something to do with parameter passing or with database permissions, the schema's owner however is set to dbo.
March 5, 2010 at 12:34 am
Solved: The components that relate to the temporary tables should have the Persist Security Info property in the OLEDB Connection manager set to False, while the rest of the transformations requiring an OLEDB Connection should read from one that has the Persist Security Info property set to True (that what was causing the OLEDB Command to fail calling the stored procedure at runtime)
regards,
Samer.
March 5, 2010 at 1:36 am
Learned something new today. Thanks for letting us know.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 18, 2013 at 1:05 pm
This worked fine. Thanks a lot.
But my problem is source and destination are both pointing to the same database and hence I had to create a new connection with same details again and the only difference between the 2 connections is "Persist Security Info". Is there any other way to resolve this issue?
July 18, 2013 at 11:45 pm
Use Windows authentication in your connection.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply