August 17, 2018 at 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?
Likes to play Chess
August 17, 2018 at 12:32 pm
VoldemarG - Friday, August 17, 2018 12:16 PMIt 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.
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
August 17, 2018 at 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?
Likes to play Chess
August 17, 2018 at 1:43 pm
VoldemarG - Friday, August 17, 2018 1:06 PMSo 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.
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
August 17, 2018 at 2:48 pm
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.
August 17, 2018 at 5:53 pm
VoldemarG - Friday, August 17, 2018 12:16 PMIt 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
Change is inevitable... Change for the better is not.
August 17, 2018 at 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.
August 18, 2018 at 9:12 am
as0917041 - Friday, August 17, 2018 11:56 PMTo 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
Change is inevitable... Change for the better is not.
August 19, 2018 at 4:31 am
Jeff Moden - Saturday, August 18, 2018 9:12 AMas0917041 - Friday, August 17, 2018 11:56 PMTo 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?
😎
August 19, 2018 at 8:00 am
Eirikur Eiriksson - Sunday, August 19, 2018 4:31 AMJeff Moden - Saturday, August 18, 2018 9:12 AMas0917041 - Friday, August 17, 2018 11:56 PMTo 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
Change is inevitable... Change for the better is not.
August 19, 2018 at 8:14 am
Jeff Moden - Sunday, August 19, 2018 8:00 AMEirikur Eiriksson - Sunday, August 19, 2018 4:31 AMJeff Moden - Saturday, August 18, 2018 9:12 AMas0917041 - Friday, August 17, 2018 11:56 PMTo 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".
August 19, 2018 at 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 :):):).
Likes to play Chess
August 20, 2018 at 6:39 am
VoldemarG - Sunday, August 19, 2018 5:39 PMWell.. 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
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply