No Columns returned by the query

Question

No Columns returned by the query

Viewing 9 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 1

The reason for the initial error is that a stored procedure doesn't have an explicit metadata contract, such as a view or a table-valued function. But SSIS does expect metadata from the source query. Hence the error.

You can try adding SET FMTONLY ON and SET NOCOUNT ON to your stored procedure, or replace the stored procedure with a table-valued function.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Sriram.RM Hall of Fame Points: 3368 More actions · Answer 2

Thanks Koen, Iam sorry missed out that info in my initial post... I tried with SET FMTONLY ON and no count is available in the procs by default..

I still dont get the column info returned

[font="Times New Roman"]For better assistance in answering your questions
Click Here[/url][/font]

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 3

Read this blog post for more background information on your problem:

http://consultingblogs.emc.com/jamiethomson/archive/2006/12/20/SSIS_3A00_-Using-stored-procedures-inside-an-OLE-DB-Source-component.aspx

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Sriram.RM Hall of Fame Points: 3368 More actions · Answer 4

I followed a similar approach to resolve this... Just that instead of using a TVF I inserted the values directly into a temp table and referenced it [the temp table[with the retain same connection property set to true]... ] in the oledb source[mainly because i was not allowed to create new permanent objects in the DB]that works fine... as i mentioned earlier the question is, there are suggestions to switch to a script component instead of these changes in the SP...

From what i understand abt the script component, we need to use a .net code and the drive needs to be ADO.net driver instead of OLEDB driver. and the .net code does a row by row insert to the buffer and exports the data to the destination

Is it wise to move to a script component to avoid the usage of temporary tables or stick to the temporary table approach to avoid RBAR

[font="Times New Roman"]For better assistance in answering your questions
Click Here[/url][/font]

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 5

Sriram.RM (7/15/2011)
I followed a similar approach to resolve this... Just that instead of using a TVF I inserted the values directly into a temp table and referenced it [the temp table[with the retain same connection property set to true]... ] in the oledb source[mainly because i was not allowed to create new permanent objects in the DB]that works fine... as i mentioned earlier the question is, there are suggestions to switch to a script component instead of these changes in the SP...
From what i understand abt the script component, we need to use a .net code and the drive needs to be ADO.net driver instead of OLEDB driver. and the .net code does a row by row insert to the buffer and exports the data to the destination
Is it wise to move to a script component to avoid the usage of temporary tables or stick to the temporary table approach to avoid RBAR

I would certainly try to avoid the RBAR. It is notoriously slow. As long as your dataset isn't that big that it chokes the tempdb with the temp table, I would go for the temp tables.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Sriram.RM Hall of Fame Points: 3368 More actions · Answer 6

I would certainly try to avoid the RBAR. It is notoriously slow. As long as your dataset isn't that big that it chokes the tempdb with the temp table, I would go for the temp tables.

Unfortunately there are datasets that return close to 2 to 3 million rows.. however even in that scenario, wouldn the temp table out perform the .net code?? or is it the other way around...

[font="Times New Roman"]For better assistance in answering your questions
Click Here[/url][/font]

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 7

Sriram.RM (7/15/2011)
I would certainly try to avoid the RBAR. It is notoriously slow. As long as your dataset isn't that big that it chokes the tempdb with the temp table, I would go for the temp tables.
Unfortunately there are datasets that return close to 2 to 3 million rows.. however even in that scenario, wouldn the temp table out perform the .net code?? or is it the other way around...

Temp tables for sure 🙂

Unless you want 3 million different transactions in your databases. 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Sriram.RM Hall of Fame Points: 3368 More actions · Answer 8

🙂 thanks... and as a side note... the Script component starts throwing timeout errors for long running queries and setting the timeout in the properties of the connection manager doesnt help... I read it somewhere stating the timeout property has to be set both in code as well as in the properties... I have no clue on how to set that property in the code though...

[font="Times New Roman"]For better assistance in answering your questions
Click Here[/url][/font]

Sunny1 SSC Eights! Points: 871 More actions · Answer 9

I had the same problem that SSIS couldn’t see the column even I can previewed the field in the SSIS.

Due to lack of permission, I couldn’t use stored procedure but t-sql script in SSIS and there were 3 temp tables in my t-sql script. I tried the 3 options in this link – http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/65112/ great tips but still didn’t work. So, I used the combination of options2 and options 3 in the link and changed my connection manager to ADO NET from OLE DB. Finally, got it working.

Thanks for all the great tips!:-P