CODEWITHSUNDEEP
sql-serverexcelssis
gavo12345
gavo12345

Reputation: 5

SSIS Export to Excel Succeeds Once Only

I've created an SSIS package with the purpose of deleting and then recreating an Excel sheet, and then inserting data from a SQL database into that Excel sheet.

What I currenty have is:

1.) An Execute SQL task to delete 'Sheet1' from a local Excel file.

2.) An Execute SQL task to recreate 'Sheet1' in the same Excel file.

3.) A Data Flow task that uses an OLE DB Source to a 2005 SQL Server database.

4.) An Excel Destinaton (using an Excel Connection Manager) which points at / maps the SQL Server columns to the local Excel spreadsheet.

So essentially I'm deleting the Excel Sheet to remove old data, recreating it, and then trying to import the SQL data into it.

This worked well when I ran it the first time. When I try to rerun it however, all the tasks run successfully, the correct number of rows are shown to move to the Excel destination on the data flow tab, but the Excel spreadsheets are blank.

Can anyone suggest why this might be happening?

Thanks, Gavin

Upvotes: 0

Views: 197

Answers (1)

Bert Wagner
Bert Wagner

Reputation: 881

Is this the only sheet in your Excel file? If so, it might be easier to use the File System Task component to delete the entire file before creating your sheet (and therefore file) in your Execute SQL statement.

file system task editor

Upvotes: 0

Related Questions