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…
It can be used to replicate the functionality in SSIS of the
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'myTable') that we use in T-SQL. That way we don’t attempt to drop a worksheet if it doesn’t exist to begin with.
Here is a quick function that I used to check the version of a spreadsheet being returned by the user. The template that I now send out for data input has a worksheet called “UserInput”, the old version doesn’t. I use this script to populate a boolean SSIS variable and depending on the result use one dataflow or the other.