Issue exporting from SQL Server 2012 to PostgreSQL

Question

I am trying to export a SQL Server 2012 database into a PostgreSQL 9.3 but am getting a weird conversion error for float columns. If the import ignores the float columns, everything goes well (about 300k rows), otherwise I get the following:

- Copy in "TABLE_TO_COPY" (Error)

Messagges
Error 0xc020844b: Data Flow Task 1: Exception during data insertion. The message returned by the provider is: Unable to cast object of type 'System.Double' to type 'System.Char[]'.
 (SQL Server Import and Export Wizard)

Errore 0xc0047022: Data Flow Task 1: Error Code SSIS DTS_E_PROCESSINPUTFAILED. Error in method ProcessInput in component "Destination - TABLE_TO_COPY" (94) with code 0xC020844B during the processing of the input "Destination Input" (97). The specified component returned an error from the method ProcessInput.  [...]

Error 0xc02020c4: Data Flow Task 1: Attempt to add a row to the buffer of activity Data flow errored with code 0xC0047020. [...]
 (SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task 1: Error Code SSIS DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Origin - PATIENT_DIALYSIS_SYMPTOM returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
 (SQL Server Import and Export Wizard)

It's not a memory issue, since the server settings have the highest possible value. What could cause this?

Null values? Did you see stackoverflow.com/questions/10209860/… ? — sqlab
– sqlab, Commented Feb 26, 2014 at 17:51
I export data through the Import/Export Wizard in SQL Server Management Studio, selecting Native Client 11 as source and an ODBC DNS pointing to the PostgreSQL database. @sqlab, I hadn't read that question before, but it looks like that might be it, is there any workaround? — frapontillo
– frapontillo, Commented Feb 27, 2014 at 7:01
As I do not know the Wizard, I am just guessing; either substitute the NULL values before and change them back or use explicit NULL in your export and import statements. — sqlab
– sqlab, Commented Feb 27, 2014 at 10:27
Where are this statements? I cannot replace NULL values with fake ones. — frapontillo
– frapontillo, Commented Feb 27, 2014 at 10:33

frapontillo · Accepted Answer · 2014-02-27 14:41:56Z

0

The issue is only present in SQL Server Management Studio's Import/Export Wizard. By resorting to the SSIS, the issue is not there.

This is the configuration I have set up for my Data Flow Task:

Source: OLE DB pointing to my SQL Server database table
Destination: OCDB connection pointing to my PostgreSQL table

It doesn't look like SSIS is able to create source tables into the origin DB, so I had to do that before setting up the project.

answered Feb 27, 2014 at 14:41

frapontillo

10.7k11 gold badges45 silver badges54 bronze badges

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

Collectives™ on Stack Overflow

Issue exporting from SQL Server 2012 to PostgreSQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related