There is a VB application that uses that Access database. I need to run the query even with application running.

The Access file is not opened by any user. The path is correct and the server has access to the folder where the Access file resides.

In reply to Remi, here is the full code. I still get an error.

Declare @tbl table (RowId int identity(1,1), ItemName varchar(100))

Insert @tbl

  Select ProductName

   FROM Products

    WHERE ProductName like...

Good article.

Why do I get an variable declaration error with this statement -

Select * from @tbl INNER JOIN Products p ON @tbl.ItemName = Products.ProductName

How do I reference the temp(#) table in the 'Destination' tab of the Transform Data Task properties ?

Am I missing something ?

If you have the 'Remove Files older than' option checked and if Sql server does not have permissions to delete, you could get this error.