Coping with No Column Names in the SSIS OLEDB Data Source Editor

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Paul Ibison » Coping with No Column Names in the SSIS OLEDB...

Coping with No Column Names in the SSIS OLEDB Data Source Editor

Rate Topic

Display Mode

Topic Options

Author

Message

Paul Ibison

Posted Wednesday, February 27, 2008 12:03 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 21, 2008 4:05 AM
Points: 291, Visits: 32

Comments posted to this topic are about the item Coping with No Column Names in the SSIS OLEDB Data Source Editor

Paul Ibison
Paul.Ibison@replicationanswers.com

Post #460705

Manie Verster

Posted Wednesday, February 27, 2008 4:16 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 7:01 AM
Points: 1,151, Visits: 878

;) What a very nice post. I tried this very same query in SQL Server 2005 because I used to have the same problem with the # tables. I did not even use the exec part of your query and it worked famously. I only had to some very minor changes.

ALTER PROCEDURE myproc

AS
begin
declare @mytable table (controlaccount tinyint, controldescription varchar(255))

INSERT INTO @mytable(controlaccount, controldescription)
SELECT controlaccount, controldescription FROM [asset types]

SELECT * FROM @mytable
end

But do not try this in MSAccess connected to SQL Server 2005. All you get back is "The stored procedure executed succesfully." even when you exec the myproc stored procedure.

Tongue

Manie Verster
Developer
Johannesburg
South Africa

Life is about choices.... I choose to be happy today

Post #460769

Tom Garth

Posted Wednesday, February 27, 2008 10:24 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, February 04, 2011 7:20 AM
Points: 977, Visits: 1,499

And if you need indexing, combine the 2 by explicity creating the temp table and indexes, and using INSERT INTO rather than SELECT INTO. This is my preferred method even without indexes. There are other reasons when a table variable is not an option. I just avoid using SELECT INTO for my own resons.

Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

Post #461089

Christian Buettner-167247

Posted Thursday, February 28, 2008 12:38 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:36 AM
Points: 2,522, Visits: 3,616

Great Article! Just one comment on indexing table variables: It actually is possible, just a little limited: You can create unique or primary key constraints on single columns.
Example:
DECLARE @MyTable TABLE (
ColA int NOT NULL PRIMARY KEY CLUSTERED
,ColB int NOT NULL UNIQUE NONCLUSTERED
)

But you cannot create such constraints on multiple columns (eg. primary key on ColA and ColB)

Best Regards,
Chris Büttner

Post #461455

Anipaul

Posted Thursday, February 28, 2008 4:24 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 2:50 AM
Points: 4,785, Visits: 1,334

Great one. Very clearly explains the problem.
Thnx

Post #461537

only4mithunc

Posted Tuesday, December 01, 2009 8:25 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 9:40 AM
Points: 358, Visits: 2,775

hi friends,

First of all I'm sorry as I'm recalling this old post.

I came accros the same problem 'Using Stored Procedure with temp table in SSIS' , and accidently I saw this old post.

From different articles I red that 3 methods to solve this issue.

1) At the start of the SP give two SET statements
SET FMTONLY OFF
SET NOCOUNT ON

Bad about this approach is when we execute the package once, the sp will get executed 5 times (by some method we can limit it to 2 only not to 1), so if insert statement is there in SP it will execute 5 times.

2) Use table variable

This case we can't create any nonclustured index on the table variable also SQL doesn't maintain any statistics for table variable.

3) Use a never true condition to trick the OLEDB , ie at the top of the SP give the condition as
if(1=2)
begin
here give the select statement with all columns in the final result with proper datatype,ie

SELECT CAST(NULL AS INT) AS C1,CAST(NULL AS VARCHAR(30)) AS C2..

end

Now I will tell you my problem, my sp has an insert to a table, so if I go for the first option it will insert more than once for each execution of the package, in my sp I need some nonclustered index on the table so I cant go with second option also.

So I preffered third option ,but in this case it will work only if we directly pass the input of the SP , ie

exec sp_name 1,'abcd'

It is not working if I pass the value through paramter,like

exec sp_name ?,? :-(

If any one has a solution for this please help me.

Once again sorry for recalling the old post.

Thanks & Regards,
MC

Thanks & Regards,
MC

Post #826730

only4mithunc

Posted Tuesday, December 01, 2009 8:40 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 9:40 AM
Points: 358, Visits: 2,775

hi friends, Smile

I got the solution also...

Inside the sp we need to give SET NOCOUNT ON ,ie

CREARE PROCEDURE SP_NAME
(@INPUT1 INT,
@INPUT2 VARCHAR(30)
)
AS
BEGIN
SET NOCOUNT ON
IF(1=2)
BEGIN
-- select all the columns in the final result
END

-- body of sp

END

Thanks&Regards,
MC

Thanks & Regards,
MC

Post #826749

Justin-14543

Posted Tuesday, March 08, 2011 9:06 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 17, 2013 11:58 AM
Points: 155, Visits: 128

I have the same exact problem except that I am using Sybase as ole db data source. Columns are not appearing. I am calling a SP with the last statement is a select from temp table. I tried solution 1 and 3 with no success. I did not try solution 2 as the data source is sybase. Any help would be appreciated.

Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.

Post #1074891

jasdeep2k

Posted Friday, April 22, 2011 10:57 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 5:32 PM
Points: 1, Visits: 133

I am usin Microsoft sql server 2008 , I tried all the 3 solutions , but every time I get the same error

"Error at Data Flow Task[OLEDB source[449]]:No colum information was returned by the sql command"

I am using the follwoing batch of sql statments to retreive the server level configuaration of all servers in my company. The table variable @tb1_SvrStng has 83 columns and it is populated using diffrenet resources.
So I summarize the sql script. I cannot use it as stored procedure because this script is going to run against 14 servers(once for each server)
So if i store the procedure on one server , other server canot able to execute that procedure in its context.
Please help me to solve my problem. I will highly appretiate your help.
I am not using any temporary tabel in my script.

declare @tb1_SvrStng table
(
srvProp_MachineName varchar(50),
srvProp_BldClrVer varchar(50),
srvProp_Collation varchar(50),
srvProp_CNPNB varchar(100),
...
xpmsver_ProdVer varchar(50),
..... .
syscnfg_UsrCon_cnfgVal int,
.....
);
insert into @tb1_SvrStng
(
srvProp_BldClrVer,
srvProp_Collation,
srvProp_CNPNB , ........
........ .
)
select convert(varchar, serverproperty('BuildClrVer')),
convert(varchar, serverproperty('Collation'))
........
.......
declare @temp_msver1 table
(
id int, name varchar(100),
...........
);

insert into @temp_msver1 exec xp_msver
Update @tb1_SvrStng
set xpmsver_ProdVer =
(
select value from @temp_msver1 where name = 'ProductVersion'
),
xpmsver_Platform =
(
select value from @temp_msver1 where name = 'Platform'
),
.....
......
select
srvProp_SerName as srvProp_SerName,
getdate() as reportDateTime,
srvProp_BldClrVer as srvProp_BldClrVer,
srvProp_Collation as srvProp_Collation,
.....
.....
from @tb1_SvrStng
Thanks
Jasdeep

Post #1097534

« Prev Topic | Next Topic »

Permissions