• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1826
  • Last Modified:

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
0
Amour22015
Asked:
Amour22015
  • 3
  • 2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>IF OBJECT_ID([tblAddress]) IS NOT NULL
Access does not have this capability.

>DROP TABLE [tblAddress]
Not sure what the DDL command would be to completely delete an Access table, but would deleting all the rows in the table work?  That would be ... DELETE * FROM tblAddress
0
 
Amour22015Author Commented:
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
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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.
0
 
Amour22015Author Commented:
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
0
 
Amour22015Author Commented:
Thanks
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now