Not displaying Columns in OLEDB Source

  • Folks,

    I have a script which has while loop and the same script working fine in Management Studio. But if i use the same set of script in OLEDB Source it not displaying any of the columns in OLEDB Source Editor.

    If i remove the while loop it display all the columns as expected. Can help me how to solve this?

    Appreciate your Help!!!

  • Why does your data source contain a WHILE loop?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Try including SET NOCOUNT ON in the begining of your SQL script that is used in OLEDB source task.

  • Phil Parkin (10/5/2010)


    Why does your data source contain a WHILE loop?

    Actually, i need to get the data's according to From and To Date from a table. So for that i used this while loop.

    Even i used NOCOUNT ON. But sorry i didn't get the columns yet.

  • sqlusers (10/5/2010)


    Phil Parkin (10/5/2010)


    Why does your data source contain a WHILE loop?

    Actually, i need to get the data's according to From and To Date from a table. So for that i used this while loop.

    Even i used NOCOUNT ON. But sorry i didn't get the columns yet.

    Why not a WHERE clause? Sounds like you're getting the records one at a time?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • I guess you are using table variable inside your SQL query. If that is the case, then OLEDB source task will not generate the columns.

    Use a stored procedure instead of the SQL script, this will generate the columns.

  • Leo Suresh (10/5/2010)


    I guess you are using table variable inside your SQL query. If that is the case, then OLEDB source task will not generate the columns.

    Use a stored procedure instead of the SQL script, this will generate the columns.

    ohh... yes, i use table variable inside. Let me check. Thanks Leo.

  • Why not a WHERE clause? Sounds like you're getting the records one at a time?

    Exactly i want to get all the data's. That'y i use loop through.

    Here in table my StartDate can be From and To date is one and the same. For ex, if i run the script today, My From and To Date

    can be today. If i run my Script tomorrow my From and To Date

    can be tomorrow.

    Exactly my script is like this.

    DECLARE @StartDate DATETIME,@LEndDate DATETIME,@EndDate DATETIME

    @StartDate = '2010-10-02'

    @LEndDate = '2010-10-05'

    WHILE @StartDate <= @LEndDate

    BEGIN

    SET @StartDate = @StartDate

    SET @EndDate = @StartDate

    <Insert Statement>

    <SELECT Statement WHERE FromDate = @StartDate AND ToDate = @EndDate>

    <Calculation Part>

    SET @StartDate = @StartDate + 1

    END

    <SELECT ST>

  • sqlusers (10/5/2010)


    Why not a WHERE clause? Sounds like you're getting the records one at a time?

    Exactly i want to get all the data's. That'y i use loop through.

    Here in table my StartDate can be From and To date is one and the same. For ex, if i run the script today, My From and To Date

    can be today. If i run my Script tomorrow my From and To Date

    can be tomorrow.

    Exactly my script is like this.

    DECLARE @StartDate DATETIME,@LEndDate DATETIME,@EndDate DATETIME

    @StartDate = '2010-10-02'

    @LEndDate = '2010-10-05'

    WHILE @StartDate <= @LEndDate

    BEGIN

    SET @StartDate = @StartDate

    SET @EndDate = @StartDate

    <Insert Statement>

    <SELECT Statement WHERE FromDate = @StartDate AND ToDate = @EndDate>

    <Calculation Part>

    SET @StartDate = @StartDate + 1

    END

    <SELECT ST>

    Why not this

    select statement

    where FromDate >= '2010-10-02' and ToDate <= '2010-10-05'

    ?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Why not this

    select statement

    where FromDate >= '2010-10-02' and ToDate <= '2010-10-05'

    ?[/quote]

    yes, Thanks Phil. I am going like Horse eye. :w00t:

  • sqlusers (10/5/2010)


    Leo Suresh (10/5/2010)


    I guess you are using table variable inside your SQL query. If that is the case, then OLEDB source task will not generate the columns.

    Use a stored procedure instead of the SQL script, this will generate the columns.

    Leo, if i create this an SP still i am not getting any columns in OLEDB Source. Do i need to set any thing?

  • Do you get any records, if you execute the SP??

    Try this...

    SET NOCOUNT ON

    Exec SP

  • sqlusers (10/5/2010)


    sqlusers (10/5/2010)


    Leo Suresh (10/5/2010)


    I guess you are using table variable inside your SQL query. If that is the case, then OLEDB source task will not generate the columns.

    Use a stored procedure instead of the SQL script, this will generate the columns.

    Leo, if i create this an SP still i am not getting any columns in OLEDB Source. Do i need to set any thing?

    First, see this thread.

    Second, as I gave you a pure T-SQL solution, why are you still using a stored proc to do this?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • First, see this thread.

    Second, as I gave you a pure T-SQL solution, why are you still using a stored proc to do this?

    Some times From and To Date be a changeable one. So i used package variable which can get the values from the users. So, i created the same as an Procedure.

    Now my OLEDB works fine and getting columns. Thanks for sharing the info. :w00t:

Viewing 14 posts - 1 through 13 (of 13 total)

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