none
SSIS package works on one machine and gives conversion error on other machine

    Question

  • Hi All,

    I have 150 + packages, I am fetching data from Oracle (11g) and loading in SQL Server. My problem is all the packages works fine on one machine whereas some of the packages give error "cannot convert between unicode and non-unicode string data types" on other machine. Please note I have implemented data conversion in all the relevant packages and I have used SSDT for the migration.

    My question is why the packages are working on one machine and not on another machine? How can I fix the issue without changing the packages (as it works on one machine) and where should I look for the problem?

    Thank you for your help!!

    Tuesday, July 08, 2014 6:04 AM

All replies

  • In both the cases are the packages pointing to same data source ie same server, database, schema table?

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Tuesday, July 08, 2014 6:07 AM
  • Yes, the source and destination server, database and schema tables are same.
    Tuesday, July 08, 2014 6:08 AM
  • Hi,

    can you please post the exact error message you are getting? From the stack (this should be included as well if available) we could see at which point ths happens.

    -Jens


    Jens K. Suessmeyer
    http://blogs.msdn.com/Jenss

    Tuesday, July 08, 2014 6:14 AM
    Moderator
  • Hi,

    I get the error at source itself. I opened the package in SSDT and when I tried to run it gives error. Also, in DFT I can see the red cross sign at source with error "Column "XYZ" cannot convert between unicode and non-unicode string data types".

    Thank you!

    Tuesday, July 08, 2014 7:04 AM
  • One more observation, the machine on which all the packages are working reads char, varchar, nvarchar and nchar data type from source as DT_WSTR whereas on other machine it reads as DT_STR and DT_WSTR.


    Wednesday, July 09, 2014 5:17 AM
  • One more observation, the machine on which all the packages are working reads char, varchar, nvarchar and nchar data type from source as DT_WSTR whereas on other machine it reads as DT_STR and DT_WSTR.


    Can you check the datatyype mapping definition file under

    <installationdrive>\Program Files\Microsoft SQL Server\110\DTS\MappingFiles

    and see if two systems have any difference in file contents for Oracle to SSIS mapping?


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, July 09, 2014 5:28 AM
  • MappingFiles seems to be same.

    Just FYI, we are using Oracle Provider for OLE DB.

    Wednesday, July 09, 2014 1:49 PM