SSIS cannot convert between unicode and non-unicode string data types

0
votes

I am new to SSIS, and have been asked to migrate data from a Postgres database, to SQL. There is no data transformation requirements. But I have hit an early snag.

I've create a System DSN on my machine for an ODBC driver to connect to the Postgres database, and it connects fine.

In SSIS, I created an ODBC Data Provider connection, and used the DSN I created. Connects well. I then create an ADO.Net data source, and select my source data connection. I select the table I want this flow to pull from, press Preview, and I see the data. All good.

I then create an OLE DB Destionation component. I can't use an ADO.Net Destination, as one of my fields is a 'GEOMETRIC' type, and SSIS seems to have an issue with that. OLE DB allows it.

I connect the Source to the Destination, and then open the Destination item. I select the Destination table, select 'Mappings' and all is mapped perfectly.

I close that screen, but I see a red cross next to my Destination component.

"Column "pid" cannot convert between unicode and non-unicode string data types."

This seems to be the case with ALL VARCHAR fields in my database. Do I need to create a Data Conversion or something for every table, and every VARCHAR in my database? Or is there a way to fix this?

1
sql-serverpostgresqlunicodessis
just add the Data Conversion Task and for every column Source Unicode then in Data Conversion Task add DT_WSTR (column name,<<length>>) – mohan111
So, add a Transformation between the Source and Destination, add all columns, regardless of being string or INT etc? But just change the DT_WSTR columns? – Craig
No. First understand the problem. First understand which system is in unicode (NVARCHAR) and which one is non-unicode (VARCHAR). Then decide whether you are going to preserve that or not. – Nick.McDermaid
The source columns are all marked as "Unicode string [DT_WSTR]", when viewing 'Advanced Editor' in SSIS. While the destination table column type is "string [DT_STR]". The destination is the type we desire. We know all data should be stored as VARCHAR, so the destination shouldn't be changed - if that helps? – Craig
It really does sound like you need to be storing NVARCHAR in your SQL Server database. Note that the concept of what NVARCHAR is vs VARCHAR is very different in SQL Server than other DBMSs so it is necessary to use different datatypes on different DBMSs. – bobince

1 Answers

0
votes

To convert Unicode and Non-Unicode string datatypes use the Data Conversion Task and for every problematic source Unicode add String[DT_STR]