Strange Issues with SSIS

Question

Question

Stuart Goodrick · May 16, 2023

I'm trying to extract data from an IRIS database into SQL server with SSIS using the ODBC driver: InterSystems ODBC35 (ODBC-2023.1.0.229.0-win_x64.exe)

I have Unicode turned off in the ODBC settings.
I'm getting strange results - with some tables the SSIS ODBC connector will work but the ADO.net connector - using the same ODBC driver will. For other tables it's the other way round. The ADO.net sees the data as unicode and won't allow me to insert it into a non unicode table but ODBC will be fine.

In some cases I'm getting data that just looks screwed up

Anyone had similar issues? Anyone successfully using SSIS with IRIS?

Product version: IRIS 2023.1

Discussion (1)0

Log in or sign up to continue

score 0 · Answer 1 · 2023-05-17T03:22:35-04:00

Having played around for a couple of hours I'm fairly sure this is all about data types Unicode / Non Unicode and the way that SSIS guesses types. A fairly reliable workaround seems to be to use a SQL statement which explicitly casts a column to the required type rather than taking the data the way it comes.