While developing a package in BIDS 2008 if we use SNAC 10.0 driver in OLE DB Source connection, call to parameterized stored procedure fails parsing with error:
Syntax error, permission violation, or other nonspecific error
If we change the connection manager to use Microsoft OLE DB Provider for Sql Server, the same query with parameters parses fine.
The workaround that I found is to set the
RetainSameConnection
= FALSE
for the Connection Manager using SNAC 10.0.
There is another workaround to modify the .dtsx XML, but I would rather not recommend that as we do not support hand editing the package XML. However, if anyone wants to try it, here is how you can do:
If you take the <component> for the OLE DB Command and put it *before* the <component> for the OLE Source, the package will succeed. Same if you had created your OLE DB Command visually before the OLE Source in BIDS. I guess this means statements are executed in the sequential order of the XML file. This essentially means that this is really something to do with how the package components are laid out in the designer in certain exceptional cases and that too, does not seem to happen any more in SSIS 2012.
Author : Debarchan(MSFT) SQL Developer Engineer, Microsoft
Reviewed by : Snehadeep(MSFT), SQL Developer Technical Lead , Microsoft