0

I need to transfer some tables from MySQL to SQL DB. I have an ID column (data type: bigint) in MySQL which is starting from 1 and it grows incrementally. I created a bigint column in the destination table in the SQL DB, but when the filling is done, the data is randomly changing:

source column
-1
-2
-3

ideal destination column
-1
-2
-3

what really happens  
-65 
-34345 
-87643

The ODBC connector is set to Unicode.

Why does this happen?

Hadi
  • 36,233
  • 13
  • 65
  • 124
Elahe.Meydani
  • 23
  • 2
  • 6
  • Could you please provide some more details? Is the ID column an identity in SQL? Is identity insert on? The insert is being performed with an OLEDB destination? Is the id column mapped correctly? – Mark Wojciechowicz May 10 '21 at 13:48
  • I don't touch mysql but this is a recent reference question on how to preserve the identity value when loading data into SQL Server https://stackoverflow.com/questions/67370325/ssis-auto-increment-field-is-not-inserted-correctly-with-data-flow-task – billinkc May 10 '21 at 23:46
  • If this is not related to identity value, is this just a question of not using ORDER BY ? I mean, when you look at the destination table, are you just doing SELECT * FROM DESTINATION ? The results are not in any order by default. – Gigga May 13 '21 at 08:54

1 Answers1

0

The problem was ith the ODBC connector. We had to et the initial statement to:

set sql_mode= 'ANSI'

enter image description here

lemon
  • 14,875
  • 6
  • 18
  • 38
Elahe.Meydani
  • 23
  • 2
  • 6