OLE DB DataSource w\ stored procedure not populating column metadata

Hot Topic

Basic VS Advanced Integration Services
Defining a boolean dimension attribute
sseutil.exe - passing variables
Seems SCOPE_IDENTITY() returns @@identity+1 in SQL 2005
Attachement generated by xp_sendmail
accessing report server programmatically
Password Issue
Reuse of Recursive Queries
How to retrieve data from query and link the data to a data number?
Display Dynamic Column in the report

SQL Server

Problem to create assembly which depends on other dll's from SQL Server
SQL Server Business Intelligence Development Studio not found
Data sources and deployment of packages
SQL Server 2000 Updates
performance between parent-child and regular
Simulating ORACLE SEQUENCES
Job Fails
Using Visual Studio 2005 to convert Access reports to SQL
Custom Code and SQL
Backup CERTIFICATE Fails

OLE DB DataSource w\ stored procedure not populating column metadata

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....




Answer this question

OLE DB DataSource w\ stored procedure not populating column metadata

  • staja2

    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



  • Munkii

    I think OLE DB Command will execute the Store proc
  • OLE DB DataSource w\ stored procedure not populating column metadata
Answer this question