All rather interesting solutions and to some degree advanced. Being a newbie a few years back I never caught on to some of the solutions above, but I did find a quick and easy way to alleviate the issue.
I myself have run into several issues moving data from Excel into SQL Server. Most of my hangups have to do with dates.
When using dates derived from formulas (ex: creating a calendar table) I have found it easier to create a macro that inserts a single quote in front of the date value and forces the string value of the date into the cell, rather than the serial number of the date. This works well for alpha-numeric values like SSNs ZipCodes, Phone Numbers and such.
Here is some code I added to my personal workbook that helps with this. It's crude but I am the only one to use this.
Private Sub Workbook_Open()
'Reset Right Click Menu
'Create Object reference to right click cell properties menu in excel
'Caption for menu
.Caption = "Convert Values"
'Reference to macro that will be called
.OnAction = ThisWorkbook.Name & "!PasteSpecial"
.Tag = "PasteValues" 'Tag reference not needed
.BeginGroup = True
Dim rng_Selection As Range
Set rng_Selection = Selection
For Each Cell In Range(rng_Selection.Address)
Cell.Value = "'" & Cell.Value
I usually create my insert/update statements inside of excel so the single quote never shows up, so I do not know if this works well with SSIS. I only have the Express version and I can't really test. I would be willing to bet that it does not affect it since excel never prints a single quote by itself, and primarily uses it to force string values when there is only 1 quote in the beginning.