Conversion issue in dtsx package
I have a dtsx package that does a data conversion task and import into SQL Server 2008 R2 database. I am having an inconsistent problem with a user, and since I am unable to change their behavior, I would like to avoid the problem. I have a column in an Excel worksheet that is created by the client. The column is a comment field. On a number of occasions, they have chosen to simply enter a numeric or currency value into the comment cell. I have suggested that they use their words but my requests have not been well received. What I need to do is have the package disregard what it is seeing and treat it as a string field regardless. When I have a normal string in the cell, the system operates as expected. I have tried doing a conversion to an unicode string(DT_WSTR), unicode text stream (DT_NEXT) and string (DT_STR).
If there is additional information I should provide I would appreciate knowing and I will endeavor to get it.
This is the first time I have done a dtsx package for this purpose, I have done mostly moves and deletes up to this point.
In the Excel Source Data Source, I would change the Data access mode to SQL Command. Then I would write a SELECT statement using CStr ( dodgy_comment) AS comment etc. Note that column names need to be enclosed in back-quotes `.