SQL Server Import and Export Wizard Error when exporting to PostgreSQL

Question

I am trying to export a SQL Server database using SQL Server's Import and Export Wizard (SQL Server 2008 R2). The database schema gets created fine in the destination PostgreSQL database and so does most of the data. It only crashes when I try to export a column which has a NULL value in the first row:

Error 0xc020844b: Data Flow Task 22: An exception has occurred during data insertion, the message returned from the provider is: Kan een object van het type System.Int32 niet converteren naar het type System.Char[]. (SQL Server Import and Export Wizard)

Unfortunately the relevant exception is in Dutch, but it says it can't convert an object of type System.Int32 to System.Char[]. The type of the column in the original SQL Server schema is int and integer in the resulting PostgreSQL database.

This only happens when the value of a column in the first row is NULL. I think it tries to infer the type of the data based on the first value of the column and defaults to System.Char[] if the first value is NULL. Is there any way to change this behaviour and let the type of the data be inferred in the same way as the type of the resulting PostgreSQL column (because it does that correctly)?

You could try to replace all null values with dummy values in SQL server and replace them back once migrated. — Erwin Brandstetter
– Erwin Brandstetter, Commented Apr 18, 2012 at 13:12
@ErwinBrandstetter Yes I could do that, I wish there were an easier way though. — Rian Schmits
– Rian Schmits, Commented Apr 18, 2012 at 13:30
You could give it a try to find out if that is indeed what causes the problem. — Erwin Brandstetter
– Erwin Brandstetter, Commented Apr 18, 2012 at 14:02
@ErwinBrandstetter It does work if I do that, but it's annoying that I have to. — Rian Schmits
– Rian Schmits, Commented Apr 18, 2012 at 14:13

kgrittn · Accepted Answer · 2012-04-18 16:33:27Z

2

The problem doesn't seem to be on the PostgreSQL side, so you should probably be looking to the conversion tool for the bug. From psql:

test=# CREATE TABLE import (id int NOT NULL PRIMARY KEY, val1 int, val2 int);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "import_pkey" for table "import"
CREATE TABLE
test=# COPY import FROM STDIN (FORMAT CSV);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1,,111
>> 2,22,222
>> \.
test=# SELECT * FROM import;
 id | val1 | val2 
----+------+------
  1 |      |  111
  2 |   22 |  222
(2 rows)

answered Apr 18, 2012 at 16:33

kgrittn

19.8k4 gold badges41 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rian Schmits Over a year ago

Yes the problem is in the conversion tool that comes with SQL Server. I just think it's strange the same tool converts the database schema correctly but not the data.

Collectives™ on Stack Overflow

SQL Server Import and Export Wizard Error when exporting to PostgreSQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related