I'm using VS2010 BIDS, importing from Excel 97-2003 .xls worksheet. I've got the following config:Excel Source -> Conversion Split Transform -> OLE DB destination. As long as I exclude the column in question the package runs and all columns import. However, if I enable the mapping for the column in question the excel source chokes on it.

The column in the source document contains letters, numbers, hash marks (#), and dollar signs($) etc. So, I have the destination column set to nvarchar(max) and have also tried nvarchar(255) up to nvarchar(800) but this column just won't import.

Within excel I have tried setting the column in question from General to Text format with no improvement. Because excel source chokes on this particular column I have found that adding a data conversion transform and doing a redirect on failure after the excel source transform to be useless.

Can you provide suggestions or explanation for what could be going on with this excel source column that makes ssis excel source transform choke on it???

Are you sure it's choking on the destination? In the advanced editor, you might want to check both the external column and the output column on the excel spreadsheet and the external column on the destination. These default to 50, IIRC, and may need to be altered if the column is larger.

No, it isn't destination related. The excel source just shuts the ssis operation down ie. won't bring over anything if I activate that particular column in the mapping of Excel Source transform.

If I enable the mapping for this column in the Excel source and subsequently on the OLE DB destination, then it fails at the source. If I disable the mapping on each end then all the other columns flow.

Nothing happens downstream of the Excel Source the moment I activate that one column.

That is why within excel I have been trying various column formats: custom, general, text. Here's a sample of what one may find in that column within Excel:Backflow - Plumbing, 2.5"inch > Penthouse Mechanical Room

for what it's worth, advanced editor for the excel source has the datatype property for this column set to Unicode string [DT_WSTR], 255. The destination is varchar(max). However, as I say, if this column is enabled, the package fails at source.

So, in the excel source, in the advanced editor, it probably shows a mismatch between the actual column size (which isn't shown anywhere), the external column size and the output column size. Truncation will occur, and the task will stop, when it tries to move the external column to the output column. If you right click on the task and bring up the advanced editor, you can change the column lengths for those 2 entries and that may take care of the problem.

If you can run the package in debug mode, you'll probably get better error messages.

thanks for hanging in with me. First, correction: the destination column is set to nvarchar so it too is Unicode.

Next, I went to excel source advanced editor and attempted to edit the external metadata for that column from Unicode string [DT_WSTR] 255 to 800. But, external metadata just reverts back to 255 when I close the dialogue box. I upped the metadata for Excel source column's output to 800 and while it 'sticks' the package still fails at source.

I have been in scenarios where the Excel source transform passes the data through and then a column fails downs stream, and for this scenario I use data conversion transforms. But I am not getting past the ingestation stage even.