Problem uploading data into SQL Server with SSIS

Hi

I designed a package in SSIS that loads data from an Excel source into a oledb SQL database table.

The problem I have is that two of the columns that are supposed to be nvarchar type columns sometimes contain numerical strings. These values causes an error when 'Allow Null' is not set and loads as NULL if 'Allow Null' is set.

How can I force these values into the table?

I have a script transformation object in the package with this code:

If IsNumeric(Row.OCCode) Then

Row.ItemCode = CStr(Row.OCCode)

Else

Row.ItemCode = Row.OCCode

End If

ItemCode is of type unicode string and the column in the SQL DB Table it's mapped to is of type nvarchar

What is the source data type set to for those columns in the source connection?|||

I don't know how to check that, but the input columns into the transformation from the source are unicode strings.

I Also tried Forcing the Excel columns to Text.

|||In the source, the columns are set to Unicode string [DT_WSTR]|||Yeah, I don't understand what NULLs have to do with numeric data appearing in a string field. Can you shed more light on this?|||

It seems impossible to upload Text and numeric values into one Text column in the SQL database. The excel source picks up the source data as unicode string if its alphanumeric values and Numeric type when it's numeric value. if I make all the source columns in the source Unicode String it stops the running package as soon as it comes accross a numeric value with this Error Message:

There was an error with input column "ItemDescription" (889) on input "OLE DB Destination Input" (248). The column status returned was: "The value violated the integrity constraints for the column

If I make the column accept NULL values in the database, the package completes the data load, but make all the numeric values NULL.

I can enter data manually into this database to take numeric values in the nvarchar field, but I don't know how to set the SSIS package to upload the numeric values as Text.

|||Take a look here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1277733&SiteID=1|||

I managed to upload the data, thanks. The link you added mentioned that numerical strings in the excel file need to be re-entered after the column format is changed to Text. Seeing that I had 54000 rows, I had to perform a text-function operation on all the relative cells. Something like =trim(A2) and then copy these cells back to the original columns as values.

Thanks

|||The important thing in that link was the setting of the IMEX parameter.|||

Yeah I saw the code, but don't know where to add it or how to change the IMEX property. I had to get the data in.

It will help a lot if you can explain where to change this property. I can change the registry entries.