If you are trying to execute some Jet SQL DDL like CREATE TABLE or DROP TABLE against an Excel workbook and you get this message:
Executing the query “DROP TABLE [Sheet1];” failed with the following error: “Cannot modify the design of table ‘Sheet1′. It is in a read-only database.”.
I have a SQL generation package that I am using to create sql files that will later be executed against the production environment. It is necessary to do this for corporate IT policy reasons which I won’t go into, however, suffice to say it isn’t the most efficient way to get new data into the database!
The data is input into Excel by the users and then passed to me the run the SSIS package. This worked fine until recently when one of the Excel spreadsheet I recieved had cells in it that were in excess of 8000 characters wide (containing Legal T&Cs).
Amazon Link: The Rational Guide to Extending SSIS 2005 with Script (Rational Guides)
A quick nugget… If you get this error:
[DTS.Pipeline] Error: component “OLE DB Source” (79) failed the pre-execute phase and returned error code 0xC0010001
Check that your variables are mapped and that they can be read at run time. Similarly if you get the same error code with the description:
Configuration from a parent variable “XXXXX” did not occur because there was no parent variable. Error code: 0xC0010001.
Check that you have spelt and referenced the correct parent variable.
During a recent data migration from Oracle to SQL Server, I came accross the problem of trying to import Greek encoded strings into our SQL Server database. With the standard character set installed with my Oracle client, all I got was upside down question marks with the odd square and ampersand thrown in for good measure.
Came across an irritating behaviour with the Excel source in SSIS. The Excel ODBC Jet Driver that SSIS uses has the annoying default configuration of only checking the first 8 lines in a spreadsheet, to evaluate the field length of any external columns in the Excel source task…
