May 25, 2011 at 8:22 am
I find this strange... Have query, which inserts Data into temp variable tables.
Went through some forums regarding this Warning (below)
The component reported the following warnings:
Error at DTSTask_DTSDataPumpTask_2 [OLE DB Source [37]]: No column information was returned by the SQL command.
Choose OK if you want to continue with the operation.
Choose Cancel if you want to stop the operation.
I was told to include SET FMTONLY ON, but couldn't find a solution for this problem. When I do this it still gives me warning message(above) and column names of the query appears when hitting the PERVIEW. No columns are returned in Column tab of OLEDB Source, All it says NAME column ( this is not the column it should return, it has to return several columns).
Please let me know, if i am not clear...Thank you
May 26, 2011 at 6:13 am
Are you using a stored procedure?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 26, 2011 at 8:23 am
nope...using a query where in, creating Table variable
May 29, 2011 at 10:46 pm
Im facing a similar issue as well... Im calling a SP inside an OLEDB source and the structure of the SP goes like this..
Create proc usp_procforssis
as
begin
Exec USP_Logwrite @param1,@param2,@param3
Select * from tableName
end
When I try to preview the data im getting the error "No column name is returned by the SQL command"... and i need the output of the select statement in a flat file.
Kinda stuck in this.. set fmtonly doesn help as well..
May 29, 2011 at 11:24 pm
@quillis: is it possible to post the query?
@sririam: read this blog post: http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/stored-procedures-are-not-parameterized-views.aspx
It is best to avoid stored procedures in the OLE DB source, as they do not garantuee metadata output.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 29, 2011 at 11:36 pm
Thanks Koen for your reply.. however im not in a position to go for an alternative method in this case... and after breaking my head with that issue i found something else..
Create proc usp_procforssis
as
begin
-- Exec USP_Logwrite @param1,@param2,@param3
Select * from tableName
end
Once i comment the logwrite SP inside the usp_procforssis, Im able to get the appropriate column values[output of the select statement]... Any thoughts on y this occurs and is there any other way i can get this up and running?
May 29, 2011 at 11:42 pm
The EXEC is screwing with your metadata, as the first statement in your sp is not a select statement.
Read the following article:
http://sqlserverpedia.com/blog/sql-server-bloggers/ssis-stored-procedure-metadata/
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 30, 2011 at 12:14 am
June 2, 2011 at 5:18 am
found a work around..... thought might be helpful to others.
Add an empty result set at the top with the same structure as the select statement, this will return the metadata to the OLEDB source.
Create proc someproc
as
begin
select '' as output
exec someproc_to_do_something
select name as output from table_to_retrieve
end
June 2, 2011 at 5:33 am
Sriram.RM (6/2/2011)
found a work around..... thought might be helpful to others.Add an empty result set at the top with the same structure as the select statement, this will return the metadata to the OLEDB source.
Create proc someproc
as
begin
select '' as output
exec someproc_to_do_something
select name as output from table_to_retrieve
end
I suggest that you try following the link posted by Koen a few posts above yours - it describes the same principle you suggest, but explains it better with better examples (sorry:-))
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 2, 2011 at 10:19 pm
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply