?
Solved

SSIS:  OLE DB Source object can't read columns of a EXEC SP that contains temp tables (Hash Table?)

Posted on 2008-06-23
5
Medium Priority
?
862 Views
Last Modified: 2013-11-30
Hi All

SSIS - I am trying to insert the results of a ole db source/stored proc, into a ole db destination/table.
In the ole db source object, Connection Manager, I have SQL Command/ my SP typed out, and hitting the 'Preview' button shows the returned recordset as expected.

Problem:  In the ole db source object, Columns, no columns are visible.  

I have heard that this may be because I am using temp tables in my SP, that I have to replace them with 'hash tables', but I am not familar with these.

Question:  Does using temp tables cause problems in DTS/SSIS?  

Thanks in advance.
Jim
0
Comment
Question by:Jim Horn
  • 3
5 Comments
 
LVL 4

Accepted Solution

by:
zx10r earned 2000 total points
ID: 21848642
make sure you have the following

SET NOCOUNT ON
SET FMTONLY OFF
0
 
LVL 4

Expert Comment

by:zx10r
ID: 21848704
SET FMT ONLY OFF
SET NOCOUNT ON
EXECUTE sp_ProcName

or you can add it directly in your procedure
0
 
LVL 66

Author Comment

by:Jim Horn
ID: 21848858
>SET NOCOUNT ON
Already had it.

>SET FMTONLY OFF
Wowwie, that works great.  Thanks.
0
 
LVL 4

Expert Comment

by:zx10r
ID: 21848905
I had the same problem a week ago. So yeah, i know how you feel =)
0
 
LVL 14

Expert Comment

by:Jagdish Devaku
ID: 21849000
Hi,

I think using of table variables will solve the issue.

So  replace the temp tables with table variables. (replace #temp table with @table variable)

all the best.

0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question