IF NOT EXISTS - SSIS

[grnzbra]

I am working through the SSIS exercises in Microsoft's SQL Server 2005 Adminisrators' Companion. The first task in the package has this SQL statement creatted by SQL Server

IF NOT EXISTS(SELECT * FROM sys.schemas WHERE name = N'HumanResources')
BEGIN
EXEC(N'CREATE SCHEMA [HumanResources]')
END
CREATE TABLE [MyAdventureWorks].[HumanResources].[Department] (
[DepartmentID] smallint NOT NULL,
[Name] nvarchar(50) NOT NULL,
[GroupName] nvarchar(50) NOT NULL,
[ModifiedDate] datetime NOT NULL
)
GO

After the first run it fails because it keeps trying to create the table. When I run just the SELECT * FROM sys.schemas statement without the WHERE clause, I get a recordset of 14 records, the mame field of none contain anything that even looks like N'HumanResources". The database conttains one table called HumanResources.Department which was created on the first run.

Does anyone have any ideas about what's going on?
The operating system is Win 7 Professional 64 bit.

 

[grnzbra]

OK.
See the problem.
The IF statement, which SQL server (or perhaps it was Visual Studio, since I was in Business Intelligence Development Studio) added as a result of changing the name of the task from Preparation SQL Task to SQL Create Department Table If Not Exists, only includes the Create Schema statement. The Create Table statement shows up afterward, so it always runs.

Still don't know why HumanResources didn't show up in sys.schemas yesterday.

 

[SQLBill]

BTW-for the best help with SSIS packages, Tek-Tips has a Microsoft SQL Server: Integration Services (SSIS) forum.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875