SSIS Data Source Data Types

Question

I am going to create a package of packages for importing data from our ERP system running on Informix IDS 11.7 to SQL Server (2012).

Using SSIS to import this data, I ran into a problem. I can access the data in two ways using the ODBC connection and the ADO.NET data source, or using the OLEDB connection and the provider.

Using ODBC is about 3 times slower (conservatively!), So I naturally want to get away from this.

However, the problem is that when I use OLEDB, the data source reports the wrong data types.

The NVARCHAR data types on the server are reported as VARCHAR (DT_STR) for SSIS. This leads to problems when importing data, as when any data arrives in unicode, it leads to a packet failure. There is no way to do data transformations here, the package does not work when the data falls on the data source component. I can install the component to ignore these errors, and it will work fine, but with missing data, which is unacceptable at all.

I tried setting DB_LOCALE and CLIENT_LOCAL to setnet32, has no effect.

When you create an OLEDB data source, it complains that the default code page cannot be found, so you need to set the “AlwyasUseDefaultCodePage” property to true so that this warning disappears, however setting the default code page does not seem to actually change its behavior. it is still trying to transfer this data as VARCHAR, even if I set the code page to something like 65001.

So my question is: how can I either correctly get the Informix OLEDB driver to work, or install / make SSIS think that this data is actually DT_WSTR, not DT_STR?

Thanks!

Answer 1

So, to summarize the steps needed to get this to work (at least for posterity).

When setting up your OLEDB connection, you need to add 2 parameters -

RSASWS=TRUE; UNICODE=TRUE; 

These 2 parameters are NOT displayed in the GUI for connecting, at least with the Informix 4.1 driver.

To add them, you need to change the ConnectionString property for the connection by adding these 2 properties to the end of the connection string. Keep in mind that this property is overwritten every time you open the connection GUI. You will need to make sure that you manually change the connection string after each input of this interface.

Another parameter for setting the connection string is the use of variables (or parameters in SSIS for SQL 2012), so any changes that are automatically made to the connection string will be fixed at runtime (they are really fixed at design time when using parameters).

One last warning I found with this data source seems to fill nvarchar with empty spaces, as if they were nchar's. This may be a problem with my source data, though, but there is something to check if you configure it, you may need to add a trimmer.