locked
Intergration Services gets error RRS feed

  • Question

  • Hello,

    I am doing an integration service to get the data from a function in SQL 2008 R2 to a temp table in SQL 2000. I got an error message as "Error at Data Flow Task [OLE DB Destination [200]]: Columns "T18_HRP_PRIMARY_DEPT" and "Temp_Department" cannot convert between unicode and non-unicode string data types.

    The T18_HRP_PRIMARY_DEPT is a function in SQL 2008.

    The "Temp_Department" is a field in a table in SQL 2000.

    Now, I used "Data Conversion" to convert the T18_HRP_PRIMARY_DEPT TO string[DT_STR] data type before it map to Temp_department field in SQL 2000. However, I still get the same error message as above.

    Have you seen this situation before? How do you solve this error?

    Thank you so much


    Madison


    • Edited by Madison Le Friday, February 3, 2012 9:35 PM
    Friday, February 3, 2012 9:32 PM

Answers

  • First of all you should post that error in SSIS forum  and not in the DataBase Mirroring

    In order to solve the problem we need more information.. What does T18.... function do? Is that possible to create a temporary table with columns have the same COLLATION as SQL Server 2008R2 table has??


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by amber zhang Monday, February 13, 2012 6:14 AM
    Sunday, February 5, 2012 7:22 AM

All replies

  • I would suggest you to create a temporary table with same data types with source table on destination database then pull data across with SSIS packages  then from temporary table push data with T-SQL using cast to destination tables.
    http://uk.linkedin.com/in/ramjaddu
    • Edited by RamJaddu Saturday, February 4, 2012 4:30 PM
    Saturday, February 4, 2012 4:30 PM
  • First of all you should post that error in SSIS forum  and not in the DataBase Mirroring

    In order to solve the problem we need more information.. What does T18.... function do? Is that possible to create a temporary table with columns have the same COLLATION as SQL Server 2008R2 table has??


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by amber zhang Monday, February 13, 2012 6:14 AM
    Sunday, February 5, 2012 7:22 AM
  • Yes, that would work. I just didn't think this way before.

    Thanks


    Madison

    Tuesday, February 7, 2012 2:51 PM