Problem after Migration with Collation

• info 58414

  Hall of Fame

  Points: 3386

  More actions

  August 23, 2017 at 6:34 am

  #339460

  We have migrated from SQL2008 to SQL2016. Incl. VS2008 to VS 2015 (SSIS)
  Our SCD component in the packages inserts records into the DIM table, even though this data record is already there (but in different spelling-large-small). Example: summer! = Summer
  However, the COLLATE SQL_Latin1_General_CP1_CI_AS is set to DB default.
  What can be the reason for this?
  SSIS Provider = sqls OLEDB

  Thanks
  Regards
  Nicole; :unsure:

• Thom A

  SSC Guru

  Points: 99137

  More actions

  August 23, 2017 at 6:45 am

  #1956360

  Do you have a different collation on the specific column, as this can be different to the database's. Does this return any results?
  USE [YourDatabase];
GO
SELECT *
FROM sys.tables t
    JOIN sys.columns c ON t.object_id = c.object_id
WHERE t.name = 'YourTableName'
AND c.collation_name != 'SQL_Latin1_General_CP1_CI_AS';
GO


  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk