SSIS exporting to Excel problem - 08-06-2010 , 08:41 AM
I'm using SSIS 2005 to export data to Excel. I'm getting a lot of instances
in which SSIS is complaining that the file is open by another process or is a
read only file when trying to create the excel file, load the file, or
perform an NTFS operation such as delete or move. I have the readonly=0 in
my Excel connection string and have even put steps ahead of the problem steps
that execute a Waitfor to give SSIS the time to release the hold on the Excel
file when moving from one step to another.
The Excel file typically is being created on a file server and the SSIS
package runs on the DB server.
My typical steps in the SSIS package are create the Excel file, populate the
file, email the file using a Script, then move or delete the file.
Below are some of the error messages I get:
"Cannot modify the design of table 'Excel Destination'. It is in a
read-only database.". Possible failure reasons: Problems with the query,
"ResultSet" property not set correctly, parameters not set correctly, or
connection not established correctly."
"Operation must use an updateable query."
This is a sporadic problem. I can run the job via dtexec and it fails with
an error above. Then I can run it again and it works fine. Same thing in
Any help would be appreciated! Thanks!
waltmallon (AT) hotmail (DOT) com