Hi,

I have this expression 

"IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 
'StagingPreChecks' AND TABLE_NAME = '" + (DT_STR,50,1252)@Item1DataFileID + "') DROP TABLE StagingPreChecks.[" + (DT_STR,50,1252)@Item1DataFileID + "]

"
+
"CREATE TABLE " + @Item1TableName + "
(RowID BIGINT IDENTITY(1,1)
,[1] NVARCHAR(201)
,[2] NVARCHAR(201)
,[3] NVARCHAR(201)
,[4] NVARCHAR(201)
,[5] NVARCHAR(201)
,[6] NVARCHAR(201)
,[7] NVARCHAR(201)
)"

which generates this query

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 
'StagingPreChecks' AND TABLE_NAME = '1') DROP TABLE StagingPreChecks.[1]

CREATE TABLE StagingPreChecks.[1]
(RowID BIGINT IDENTITY(1,1)
,[1] NVARCHAR(201)
,[2] NVARCHAR(201)
,[3] NVARCHAR(201)
,[4] NVARCHAR(201)
,[5] NVARCHAR(201)
,[6] NVARCHAR(201)
,[7] NVARCHAR(201)
)

In the package Delay Validation is set to True. It is working on Dev Machine. But some how it is not checking the table existence. It says in SSIS log this message

Executing the query "IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES..." failed with the following error: "There is already an object named '1' in the database.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The 'Delay validation' here is redundant.

You did a typo

above it is @Item1DataFileID in the check portion and @Item1TableName in CREATE table

Hi Arthur,

thanks for your reply. It is not typo, actually Table is being created by concatenating two variables i.e. 

@Item1TableName and @Item1DataFileID . 

What do you mean by The 'Delay validation' here is redundant.. How is it redundant?

Regards,

MH

Do you have another object in the above schema called "1"?  A view or stored procedure?  Your if statement won't find, let alone drop anything but a table with that name but you will not be able to create a table with the same name as another object.