Drop and Recreate Excel table

I'm having a heck of a time trying to upload data to an excel spreadsheet. This works perfectly in sql 2000 but I've been having problems with 2005

SSIS package "Package1.dtsx" starting.
Error: 0xC002F210 at Drop table(s) SQL Task, Execute SQL Task: Executing the query "drop table `GRE`
" failed with the following error: "Table 'xxx' does not exist.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Drop table(s) SQL Task
Error: 0xC002F210 at Preparation SQL Task, Execute SQL Task: Executing the query "CREATE TABLE `xxx` (
`TEST_REC_NBR` Decimal(29,0),
`PROCESS_DT_GRE` LongText
)
" failed with the following error: "Invalid precision for decimal data type.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Preparation SQL Task
SSIS package "Package1.dtsx" finished: Failure.

It looks like the problem is Decimal (29,0) - seems it is not a valid precision for excel. I tried a create table statement with Decimal (28,0) and it succeeded while Decimal(29,0) gave the same error. I think the maximum precision Excel supports for Decimal is 28.|||This is the output when I choose create and drop the table AND when I try to delete the rows. I've changed the field to decimal(28,0) also.
I can get around the problem by using a file system task and removing the file. This will work for what I'm doing but what if I need to delete only certain rows?
- Validating (Error)
Messages
Error 0xc001000e: {5143E747-D851-4E0F-9335-CCBF5E66371E}: The connection "DestinationConnectionOLEDB" is not found. This error is thrown by Connections collection when the specific connection element is not found.
(SQL Server Import and Export Wizard)
Error 0xc001000e: {5143E747-D851-4E0F-9335-CCBF5E66371E}: The connection "DestinationConnectionOLEDB" is not found. This error is thrown by Connections collection when the specific connection element is not found.
(SQL Server Import and Export Wizard)
Error 0xc00291eb: Drop table(s) SQL Task: Connection manager "DestinationConnectionOLEDB" does not exist.
(SQL Server Import and Export Wizard)
Error 0xc0024107: Drop table(s) SQL Task: There were errors during task validation.
(SQL Server Import and Export Wizard)

|||

I think you have hit a bug in Import Export Wizard, where the connection for the Execute SQL Task which drops the table is set incorrectly. I will investigate further.

|||

Ranjeeta wrote:

I think you have hit a bug in Import Export Wizard, where the connection for the Execute SQL Task which drops the table is set incorrectly. I will investigate further.

I am having trouble deleting Excel 2007 sheets programmatically. In the earlier versions of Excel, I used to open an ADO connection and executed some code like below

strSQL = "DROP TABLE NameOfExcelSheet;"

objCommand = New OleDb.OleDbCommand(strSQL, cnn)

objCommand.ExecuteNonQuery()

By executing the above code I could delete the sheet I wanted from an Excel workbook. But this method does NOT work with Excel 2007.

How can I programmatically delete sheets from an Excel 2007 workbook?

|||

Sorry... please ignore my previous comments and question.

strSQL = "DROP TABLE NameOfExcelSheet;" only clears the contects of an Excel sheet and does not delete the sheet itself on all versions of Excel.