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

  • 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

  • ๐Ÿ˜‰ 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)

  • 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
  • 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

  • Great one. Very clearly explains the problem.

    Thnx

  • 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

  • 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

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

  • 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

  • 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

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

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