Error when Try to Drop Excel Sheet

SQL Server Administration
1

Hi all,
In my SSIS package, I'm trying to drop and recreate the excel worksheet before it gets populated by the next task.
I know the Excel connection is valid because the package inserts rows to the worksheet for that same Excel Connection.
Drop table Sheet1
go;

CREATE TABLE SHEET1
('FirstName' LongText,
'LastName' LongText,
'FreightBillRecdDate' DateTime,
'TotalBillsProcessed' Short)

But this SQL Task fails with this error:

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft Access Database Engine" Hresult: 0x80004005 Description: "'T:\Reports\ProductionReport1\ProductionReport1.xlsx' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.".

The Excel sheet is not open. What could be causing this?
Thanks

2

The error is not aboit whether the file is open or not. It is about not finding the file in the specified path.

Does that file exist in T:\Reports\ProductionReport1\

And is that path a drive on the server itself?

3

@yosiasz The excel sheet is being written to by the same package. But of course each run appends to the existing rows. I've added the Drop Table task which uses the same Excel connection. Yes the file exists
at T:\Reports\ProductionReport1 which is a networkpath on both my laptop and the SQL Server.
I'm running this in Debug mode on my laptop when the failure occurs.

image
image1065×522 12.4 KB

4

I'm running this package on my laptop. It has not yet been deployed.

The SQL Task which attempts to Drop Table gives this error:

[Execute SQL Task] Error: Failed to acquire connection "Excel Connection Manager". Connection may not be configured correctly or you may not have the right permissions on this connection.

But it doesn't seem like a permissions issue because the task which writes to the Excel sheet succeeds. Any ideas?

5

I was running Visual Studio as Administrator. That's why it couldn't find the network path.

So now my error is Table Sheet1 does not exist when SQL task Drop Table Sheet1 runs. I tried Sheet1$ and get the same error. Sheett1 clearly does exist. Any ideas?

7

how do you know?
maybe previous manual run failed hence it was deleted. I would use .net if I was you for this, script task in SSIS