asked on

SSIS 2005 - IF Table Exist Statement (Microsoft Access SQL)

Hi and Thanks,

This is a Execute SQL Task inside of SSIS 2005 using SQLStatement properties which calls a ACCESS DATABASE.

I have this code:
IF OBJECT_ID('tblAddress') IS NOT NULL
DROP TABLE 'tblAddress'
GO

This does not work.
I get an error:
DROP TABLE 'tblAddress'
" failed with the following error: "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Drop Tables

I have also tryed:
IF OBJECT_ID([tblAddress]) IS NOT NULL
DROP TABLE [tblAddress]

Same problem.

Before this statement I link to a Access 2003 database using the Connection Manager.
Please see attachment:
ChkTab

Thank you for helping
ChkTab.docx

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Amour22015

ASKER

jimhorn,

This part works fine:
DROP TABLE [Tablename]

It is this part that is giving the problem:
IF OBJECT_ID([tblAddress]) IS NOT NULL

But from what you are saying there is no way to test for existing table using SQL for an Access DB?

Thanks

Jim Horn

>there is no way to test for existing table using SQL for an Access DB?
None that I'm aware off as T-SQL.

Using Access VBA and either DAO or ADOX you can loop through the tabledefs, and determine if the table exists or not. Let me know if that's an acceptable workaround, and I'll post some code.

Amour22015

ASKER

jimhorn,

I don't want to get that involved with this problem. Trying to get away from using Access in the future, so all this is going to be converted anyways.

Thanks

Amour22015

ASKER

Thanks