Annoying error message 'Cannot drop table because table does not exist' in ExecuteSQL Task BIDS 2008.

  • It happens even though I include IF EXISTS statement before before Drop Table.

    IF EXISTS (SELECT Name from sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MasterAgents]') AND type in (N'U'))
    DROP TABLE [dbo].[MasterAgents]
    GO

    -- then the Create Table statement follows.
    ..

    What's more annoying is that package breaks with red coloring of Exec SQL task even if I set Delay Validation = True on both Exec SQL task and the Sequence Container containing it..

    Any workarounds?

    Likes to play Chess

  • VoldemarG - Friday, August 17, 2018 12:16 PM

    It happens even though I include IF EXISTS statement before before Drop Table.

    IF EXISTS (SELECT Name from sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MasterAgents]') AND type in (N'U'))
    DROP TABLE [dbo].[MasterAgents]
    GO

    -- then the Create Table statement follows.
    ..

    What's more annoying is that package breaks with red coloring of Exec SQL task even if I set Delay Validation = True on both Exec SQL task and the Sequence Container containing it..

    Any workarounds?

    Sure, just TRUNCATE the table instead of dropping and creating it.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • So table absolutely must be pre-created?
    Which is not a good option if for example a manager wants to test package in a different environment where it d take too long to get table created by DBA or whatever else.  And sometimes keeping a 100 semi-stage/semi-temp tables permanently when they are used only for half an hour to swap and massage things around does not seem a good option either.

    Would that be an option to use a Script task to do drop/create instead of an Execute SQL task, to bypass this bug of SSIS 2008 not recognizing  an IF statement?

    Likes to play Chess

  • VoldemarG - Friday, August 17, 2018 1:06 PM

    So table absolutely must be pre-created?
    Which is not a good option if for example a manager wants to test package in a different environment where it d take too long to get table created by DBA or whatever else.  And sometimes keeping a 100 semi-stage/semi-temp tables permanently when they are used only for half an hour to swap and massage things around does not seem a good option either.

    Would that be an option to use a Script task to do drop/create instead of an Execute SQL task, to bypass this bug of SSIS 2008 not recognizing  an IF statement?

    I last used SSIS 2008 more than five years ago ... I cannot remember much about its quirks, I'm afraid.

    If I am developing packages, I always use permanent tables for staging, so I have not had to solve this particular issue.

    Repeatedly dropping and recreating tables puts an unnecessary load on the DB engine and makes debugging more difficult. I try to minimise dynamic schema changes too. Otherwise it plays havoc with drift reports.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Try 

    if object_id('tempdb..#t','U') is not null
    drop table #t;

    create table #t
    (x int
    );

    e.g. remove the GO statement (that is probably what is causing the issue)
    and the test above is simpler and gives same result.[/code]e.g. remove the GO statement (that is probably what is causing the issue) and the test above is simpler and gives same result.

  • VoldemarG - Friday, August 17, 2018 12:16 PM

    It happens even though I include IF EXISTS statement before before Drop Table.

    IF EXISTS (SELECT Name from sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MasterAgents]') AND type in (N'U'))
    DROP TABLE [dbo].[MasterAgents]
    GO

    -- then the Create Table statement follows.
    ..

    What's more annoying is that package breaks with red coloring of Exec SQL task even if I set Delay Validation = True on both Exec SQL task and the Sequence Container containing it..

    Any workarounds?

    I know little about SSIS, mostly because I do most of the things it does without using it. 😀

    As an example, the following T-SQL works fine whether or the table pre-exists or not (as of 2008).

         IF OBJECT_ID('dbo.DropMe','U') IS NOT NULL
       DROP TABLE dbo.DropMe
    ;
     CREATE TABLE dbo.DropMe
            (
            name                sysname         NOT NULL,
            object_id           int             NOT NULL,
            principal_id        int             NULL,
            schema_id           int             NOT NULL,
            parent_object_id    int             NOT NULL,
            type                char(2)         NOT NULL,
            type_desc           nvarchar(60)    NULL,
            create_date         datetime        NOT NULL,
            modify_date         datetime        NOT NULL,
            is_ms_shipped       bit             NOT NULL,
            is_published        bit             NOT NULL,
            is_schema_published bit             NOT NULL
            )
    ;
     INSERT INTO dbo.DropMe
     SELECT * FROM sys.objects
    ;

    If, for some reason, that doesn't work in SSIS, then you'll know one of the many reasons why I don't use SSIS. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • To avoid wastage of time, what you can do is try a recovery tool, if it error message occurs on frequent basis.

  • as0917041 - Friday, August 17, 2018 11:56 PM

    To avoid wastage of time, what you can do is try a recovery tool, if it error message occurs on frequent basis.

    Not sure how that comment applies here.  The OP is trying to drop and recreate a table on the fly and getting an error doing so.  What does a recovery tool have to do with any of this?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Saturday, August 18, 2018 9:12 AM

    as0917041 - Friday, August 17, 2018 11:56 PM

    To avoid wastage of time, what you can do is try a recovery tool, if it error message occurs on frequent basis.

    Not sure how that comment applies here.  The OP is trying to drop and recreate a table on the fly and getting an error doing so.  What does a recovery tool have to do with any of this?

    You'll never know, maybe the error recovery tool is meant to correct the comprehension errors of that poster?
    😎

  • Eirikur Eiriksson - Sunday, August 19, 2018 4:31 AM

    Jeff Moden - Saturday, August 18, 2018 9:12 AM

    as0917041 - Friday, August 17, 2018 11:56 PM

    To avoid wastage of time, what you can do is try a recovery tool, if it error message occurs on frequent basis.

    Not sure how that comment applies here.  The OP is trying to drop and recreate a table on the fly and getting an error doing so.  What does a recovery tool have to do with any of this?

    You'll never know, maybe the error recovery tool is meant to correct the comprehension errors of that poster?
    😎

    Sometimes it's difficult to tell.  I've seen some folks that seem to be off track for a particular discussion and then they pull a truly magical rabbit out of their hat even if they were off track.  It's almost like the discussion got someone's juices flowing and they had an epiphany about a totally different subject but the subject is a good one and leads to other learning.  Sometimes it even looks like a precursor to some spam... which is the case for this post but I see no previous signs of such a thing.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Sunday, August 19, 2018 8:00 AM

    Eirikur Eiriksson - Sunday, August 19, 2018 4:31 AM

    Jeff Moden - Saturday, August 18, 2018 9:12 AM

    as0917041 - Friday, August 17, 2018 11:56 PM

    To avoid wastage of time, what you can do is try a recovery tool, if it error message occurs on frequent basis.

    Not sure how that comment applies here.  The OP is trying to drop and recreate a table on the fly and getting an error doing so.  What does a recovery tool have to do with any of this?

    You'll never know, maybe the error recovery tool is meant to correct the comprehension errors of that poster?
    😎

    Sometimes it's difficult to tell.  I've seen some folks that seem to be off track for a particular discussion and then they pull a truly magical rabbit out of their hat even if they were off track.  It's almost like the discussion got someone's juices flowing and they had an epiphany about a totally different subject but the subject is a good one and leads to other learning.  Sometimes it even looks like a precursor to some spam... which is the case for this post but I see no previous signs of such a thing.

    In short, I've got an hammer, just have to turn everything into a nail:pinch:
    😎
    The post of course looks, smells and spells like a spam, would have reported it if there was any kind of a link or strong product reference. I think you are right, the way I'd put it would be a short (thought) circuit...

    Don't wan't to be arrogant or judgmental but I have a very little tolerance when it comes to bad or irrelevant advises, got enough of those to deal with at work by all the "vendors".

  • Well.. I realize that this whole Drop /Create issue is minor, it is only a small inconvenience, 
    an object can either be precreated or created permanently and just truncated. This may not be worth so much effort.
    But I have become obsessively intrigued, and conducted a bunch of experiments.
    and this is what I descovered:

    Either IF EXISTS (...)  or   IF OBJECT_ID..    work same way. GO after the Drop statement makes no difference compared to semicolon or nothing ...

    What really makes a diffrence is whether the EXEC SQL Task is by itself in the control flow (in such case IF EXISTS/DROP works regardless of the scenario). 
    OR within other container like Sequence container. And the more different subtasks are within a container where Exec SQL task is with Drop/Create
    the more erratically it behaves, and if table does not exists it will always through erorr.
    as opposed to Exec SQL task on the control flow with no container containing it (in which case everything works as it should).

    Not that all this is critical for SSIS development. The issue is minor.  But an obsession is now off my shoulder :):):).

    Likes to play Chess

  • VoldemarG - Sunday, August 19, 2018 5:39 PM

    Well.. I realize that this whole Drop /Create issue is minor, it is only a small inconvenience, 
    an object can either be precreated or created permanently and just truncated. This may not be worth so much effort.
    But I have become obsessively intrigued, and conducted a bunch of experiments.
    and this is what I descovered:

    Either IF EXISTS (...)  or   IF OBJECT_ID..    work same way. GO after the Drop statement makes no difference compared to semicolon or nothing ...

    What really makes a diffrence is whether the EXEC SQL Task is by itself in the control flow (in such case IF EXISTS/DROP works regardless of the scenario). 
    OR within other container like Sequence container. And the more different subtasks are within a container where Exec SQL task is with Drop/Create
    the more erratically it behaves, and if table does not exists it will always through erorr.
    as opposed to Exec SQL task on the control flow with no container containing it (in which case everything works as it should).

    Not that all this is critical for SSIS development. The issue is minor.  But an obsession is now off my shoulder :):):).

    Well, the whole drop/create thing actually is NOT a minor issue in cases where it's needed and it's needed a whole lot more than a lot of people might suspect unless they've also had the need.. 😉  It's actually incredibly important when it comes to importing, exporting, or tracking batches of data. 

    You've also identified one of the many reasons why I don't use SSIS for such things. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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