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

Question

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

Paul Ibison

SSCertifiable

Points: 5303
More actions
February 27, 2008 at 12:03 am

#120720

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

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

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

Manie Verster SSCertifiable Points: 7152 More actions · Answer 1

😉 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.

:-PManie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

Tom Garth SSCertifiable Points: 6193 More actions · Answer 2

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[/url]

"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

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 3

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

Anipaul SSC-Insane Points: 24681 More actions · Answer 4

Anipaul

SSC-Insane

Points: 24681

February 28, 2008 at 4:24 am

#783080

Great one. Very clearly explains the problem.

Thnx

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 5

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

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 6

hi friends, 🙂

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

Justin-14543 SSCrazy Points: 2017 More actions · Answer 7

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.

jasdeep2k SSC Journeyman Points: 83 More actions · Answer 8

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 , ........

........ .

)

selectconvert(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

maheshkumar_pandeti SSC Journeyman Points: 84 More actions · Answer 9

Jasdeep,

Came across same issue today for similar task you are doing and got it resolved by using ADO.Net source instead of OLEDBSource in Dataflow task.

Thanks..

Mahesh