I don’t know about you, but one of the things that drives me to distraction when writing SSIS packages is being forced to use excel as a data source. I’d like to qualify that statement by adding that it’s the use of excel in the face of all reason, to do the job that other software would be better for. Unfortunately though, corporations and people being what they are, they are a fact of life for an ETL developer.
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.”.
Normally in SSIS when we want to issue a SQL command against each row in a dataflow, we use the OLE DB Command component. When executed against SQL Server the command normally takes the form:-
UPDATE dbo.TableName SET ColumnName1 = ? WHERE ColumnName2 = ?
Where “?” can be mapped to the incoming columns in the order they are written.
However you will find that when you are using this component against an Access database, that this syntax will not work. You will get an error saying:
There is more than one data source column with the name “?”. The data source column names must be unique.
Amazon Link: Microsoft® Office Access™ 2007 Inside Out (Inside Out (Microsoft))
Given the problems inherent in loading excel files with SSIS, I thought it might be useful to share with you some code to get you started on interrogating it’s schema prior to running your Execute SQL control tasks or your dataflows.
This is especially useful for me as it allows me to test column names, worksheet names and datatypes etc prior to attempting to load the data (I have found that users have a tendency to format and rename sheets without realising the impact it has on the import). There are other uses too…
