I'm having some issues getting OLE DB Data Sources to work w\ stored procs in SSIS. Here's the situation.
I have an OLE DB Data Source set up to call a stored proc w\ no parameters. The stored procedure loops through a set of databases and inserts data from each database into a results table. I'm attempting to return the results table to SSIS, but the Available External Columns are not populating. However, previewing the query in SSIS does show results. The insert in to the results table is done by a call to sp_executesql.
I've tried setting the results table up as a temp table, table variable, and static table. I have NOCOUNT set ON and am only returning one recordset. I've seen the other threads in here about similar problems, but none of their solutions seem to work for me.
Any help would be much appreciated....
I believe that there are known problems around using SPs in OLE DB Source components. Its something to do with requiring that the query that returns the data is the first statement in the proc. This enables SSIS to understand the metadata of the returned result set.
I know this doesn't help you in your case though. I do not know if there is a workaround or not having never faced this problem before myself. I hope what I've said above goes some way to helping though.
-Jamie
|||I think OLE DB Command will execute the Store proc|||Try converting the sp to a multi-statement table-valued function, giving Data Access Mode for your OLE DB source as SQL Statement and specifying the statement SELECT * FROM your_table_valued_function(); the wizard will gussy the SQL up
No comments:
Post a Comment