Converting DT_NTEXT to DT_Text

Question

So I'm converting a date to MMDDYYYY using the following code:

replace(convert(varchar(10), Absent_Date, 101), '/', '') as Absent_Date

When I run it in Navicat it runs fine, but when I use SSIS to export from a stored procedure to a flat file it get the following error message

I have read about 20 differnt posts from here and other forum sites but haven't found one that quite works for me. I tried to use the data conversion object but I had trouble getting it to work. I honestly don't care what format it's in just as long as SSIS will let me export to a flat file I have about 20 text files with dates in them that need to be in this same format. Any help is greatly appreciated!

I'm on Sql Server 2008 R2

Answer 1

Based on the error you captured, I am assume that the metadata coming out of your source (OLE, ADO, whatever) indicates that the column Absent_Date is DT_NTEXT. The resolution then is to transform that into something that "works." I typically apply two data conversion tasks NTEXT to TEXT and then I make that TEXT into a string type. If your data is unicode, then it'd just be an NTEXT to WSTR conversion but I'm a dumb American.

Here you can see the metadata for the output from my OLE DB Source indicates Absent_Date is NTEXT. The final step (bit bucket) would show a new column STR_Absent_Date of length 10, data type of DT_STR (codepage 1252)

Taking this approach should allow you to convert your NTEXT data into something compatible with a flat file destination.