Here is something we recently ran into when using a stored procedure inside OLE DB Source Component.
A change was made to a stored procedure that was originally working and providing SSIS OLE DB Source Component with all of the mappings and meta data. After a change to that proc, the mappings could no longer be validated and the meta data was not available. when configuring the component were suddenly not seen. What happened...?
Apparently the proc was changed in such a way that the original use of a table variable to store some temp results was removed and replaced with a #temp table. This stopped the transmission or access to the metadata from the proc. After some searching Dave M. was able to find this post http://blogs.conchango.com/jamiethomson/archive/2006/12/20/SSIS_3A00_-Using-stored-procedures-inside-an-OLE-DB-Source-component.aspx
I added the SET FMTONLY OFF and tried to validate the SQL, SSIS did not like this and it threw an error. The proc was being called with 2 parameters and I could not validate with the SET FMTONLY OFF hint and the two parameters present. So what did I do...???
I punted...
Actually the proc was changed back to using the table variable and all is working again - after a host of other issues.
I plan on working on this again this week and will test out various methods to find a workaround that suits our needs. Stay tuned.
Subscribe to:
Post Comments (Atom)
1 comment:
Add the SET FMTONLY OFF into the actual proc itself and it will work.
Post a Comment