SSIS Conversion from Flat File DT_WSTR to nvarchar(255) field null when characters other than numbers

Question

I have been having an odd problem with data casting and importing from a flat file to a SQL Server database. The issue is that in certain fields, there are both integers and character strings coming through for example 9012635 and 54Z2534, 234-1823.

In the SSIS package on all of my fields has a data Type pf DT_WSTR (255) and the data is inserting into nvarchar(255). Initially I had created the package by using the import export wizzard and then made modifications (but not to the conversions. I accepted what I was given by the wizard (i.e. DT_WSTR). It ended up not importing data into those fields. It created the row and inserted data into other columns but in certain columns, it placed NULL - no data.

To test things, I ended up trying the wizard with the file. I received an error: "The last row in the sampled data is incomplete. The column or the row delimeter may be missing or the text is qualified incorrectly".

Despite this, the file still imports all teh rows, but doesn't seem to like text in those columns, and just ends up importing the integer values as text.

Other files that have not given this error message have also had the same symptoms.

I am not sure how to resolve this issue, or what the problem is.

Solution

NVARCHAR should correspond to DT_WSTR, and VARCHAR should correspond to DT_STR. Are you sure it's not a conversion problem between those two types? If you have true DT_I4 integers coming in, you should not be seeing alpha characters.

You can add a Data Viewer to your dataflow to ascertain exactly what data types you have in your SSIS package.

If you wish to convert string types, you can use a Derived Column Transform to create a new column, and then cast one string type into another.

So, for example, (DT_STR, 30, 1252)OldColumnName would cast thirty characters of an NVARCHAR to VARCHAR according to code page 1252. Alternatively, (DT_WSTR, 50)ColName would cast fifty characters of VARCHAR into NVARCHAR.