• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 869
  • Last Modified:

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

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
Jim Horn
Asked:
Jim Horn
  • 3
1 Solution
 
zx10rCommented:
make sure you have the following

SET NOCOUNT ON
SET FMTONLY OFF
0
 
zx10rCommented:
SET FMT ONLY OFF
SET NOCOUNT ON
EXECUTE sp_ProcName

or you can add it directly in your procedure
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
>SET NOCOUNT ON
Already had it.

>SET FMTONLY OFF
Wowwie, that works great.  Thanks.
0
 
zx10rCommented:
I had the same problem a week ago. So yeah, i know how you feel =)
0
 
Jagdish DevakuSr DB ArchitectCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now