SSIS & Excel (Part 2)
More Excel woes with SSIS…
Be very careful when using Excel as your transformation destination. Given that it is hard to maintain a consistent controlled version of any excel file you are using, it can be very prone to fail on account of the encoding that is used within it.
Take for example a file that is a user maintained source for some website data (yes I know it isn’t ideal but it happens). Users need to modify this file each day and each time your package runs in needs to output this data back to the same file. It is extremely tricky to do this in Excel especially if you have non-Excel datatypes (GUIDs, VARCHARS etc).
We can overcome the run-time issues where the Excel Jet Driver determines column length based on first 8 rows; and the problem with converting the string values of a GUID into something that SSIS recognises as a GUID… however, one factor that is harder to control is the problem of encoding.
If a user pastes a non-unicode value into your excel file, this will cause the destination component (and the package) to fail with an OLE DB ProcessInputMethod() error.
What you need to do is pay special attention to the change control of the source file. Consider taking the following actions:
- Allow limited access to it.
- Make a read-only copy for non-admins to read.
- Protect cells wherever possible with a password so that format can’t be changed.
- Keep a backup template, that can be substituted fairly easily.
- Break the data up into smaller chunks (split one spreadsheet into 2 or 3).
My suggestion to anyone who needs this type of functionality - is to use an MS Access database instead!