Used "Data Conversion" Transformation - VS_ISBROKEN - cannot convert between unicode and non-unicode string data types.

Question

Our Oracle source changed from Non-Unicode to Unicode. So I am using "Data Conversion" Transformation to convert the DT_WSTR to DT_STR to match the target SQL Server sting/text. 

I am getting following error. 

Error at Load Audit_Log [ViewPoint AUDIT_LOG Source [1]]: Column "Column1" cannot convert between unicode and non-unicode string data types.

Error at Load Audit_Log [ViewPoint Table Source [1]]: Column "Column2" cannot convert between unicode and non-unicode string data types.

Error at Load Table [SSIS.Pipeline]: "component "DB Table Source" (1)" failed validation and returned validation status "VS_ISBROKEN".

Error at Load Table [SSIS.Pipeline]: One or more component failed validation.

Error at Load Table : There were errors during task validation.

 (Microsoft.DataTransformationServices.VsIntegration)

We are not planning to change the Target table structure, so using the Data Conversion function to change it to Non-Unicode.

Really appreciate your thoughts.

Answer 1

Hello,

Check the links below. It may help you.

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/a5efde89-d7ae-4634-a55c-ee36df78a71a/

http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/6f738c0a-8da0-4599-b86b-63a2d1363b58/

Answer 2

You can either choose to type cast the data in the oracle source db query or you can use a data conversion transformation.

---

http://btsbee.wordpress.com/

Answer 3

If you already had your SSIS package designed, then the Oracle Source changed data types, then the SSIS Source may not have caught that change.

I would try deleting the SSIS Source you're using and drop a new one on the surface.  Configure it as you had before, and it should output Unicode types.  Then use a Data Conversion transformation if you need to.

---

Talk to me now on

Answer 4

Source identified as DT_WSTR for both output and external columns when I loaded the package with new database connection which is Unicode. Do I still need to drop and re import the source? No Errors shown at source or target in the Data Flow. But it throws error while debugging.

I am a newbie.  Thank You.

Answer 5

Thank You Patrice. But it does not help much. 

I did the same data conversion in a sample package. It works fine.

Answer 6

If removing and then adding the source back does not help, then consider revising the Code Page setting.

I just found you a valuable thread to skim thru: http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/dc1a61f2-1ab8-4ed3-b85c-db6481800b50/

Pay attention to someone saying:

... set the OLE DB Source to AlwaysUseDefaultCodePage to True.  The DefaultCodePage should be set to 1252 which is correct for Western Alphabet.  If Oracle is not using the Western Alphabet, you'll need to determine the charset used and use http://msdn.microsoft.com/library/default.asp?url=/workshop/database/tdc/reference/CharSet.asp to correctly set the default code page.

---

Arthur My Blog