SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Paul Ibison
Paul Ibison
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1649 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
Manie Verster
Manie Verster
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2933 Visits: 1044
Wink 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
Tom Garth
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2613 Visits: 1499
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

Christian Buettner-167247
Christian Buettner-167247
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6765 Visits: 3889
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
Anipaul
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11883 Visits: 1407
Great one. Very clearly explains the problem.
Thnx



only4mithunc
only4mithunc
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2986 Visits: 2803
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
only4mithunc
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2986 Visits: 2803
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
Justin-14543
SSChasing Mays
SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)

Group: General Forum Members
Points: 625 Visits: 154
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
jasdeep2k
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 174
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
maheshkumar_pandeti
maheshkumar_pandeti
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 334
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search