SQL Server DTS / SSIS and large tables "out of memory"

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
bcfletcher
Posts: 5
Joined: Thu 28 Jun 2007 03:34

SQL Server DTS / SSIS and large tables "out of memory"

Post by bcfletcher » Thu 28 Jun 2007 03:44

I am trying to use the PostgreSQLDirect driver to Migrate data from Postgres to SQL Server via DTS / SSIS. Most of the tables migrate fine however larger tables (in excess of 10 million rows) fall over with an out of memory exception. Any ideas how I can get around this, or is this a known bug with the driver? The server has 2 Gig of RAM and plenty of swap, and monitoring the memory it seems to have plenty available when it falls over.

SSIS Error

- Pre-execute (Error)
Messages
Error 0xc0047062: Data Flow Task: System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown.
at CoreLab.PostgreSql.PgSqlDataReader.e(Int32 A_0)
at CoreLab.PostgreSql.PgSqlCommand.a(CommandBehavior A_0, IDisposable A_1, Int32 A_2, Int32 A_3)
at CoreLab.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute()
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper90 wrapper) (SQL Server Import and Export Wizard)

Error 0xc004701a: Data Flow Task: component "Source - Query" (1) failed the pre-execute phase and returned error code 0x8007000E.
(SQL Server Import and Export Wizard)

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Mon 02 Jul 2007 07:39

Could you send me your SSIS project?
Also I need the structure of the table on which migration fails and the number of records in it.

bcfletcher
Posts: 5
Joined: Thu 28 Jun 2007 03:34

Post by bcfletcher » Wed 04 Jul 2007 23:59

Didnt get as far as an SSIS package - fails in the Data Import Wizard, though I did try directly from Business Development Studio and had the same problem.

The table structure is;

column1 - bigint (primary key)
column2 - bigint
column3 - smallint
column4 - text
column5 - bigint
column6 - text
column7 - smallint
column8 - smallint
column9 - text
column10 - text
column 11 - smallint
column 12 - bigint

I also tried changing the text data types on the postgres db to varchar(255) - no luck there either.

So to replicate. Create the above table and populate with 13,000,000 rows. Then create a db on SQL Server 2005 and try to import using the .NET Postgres provider using a query (select * from tablename). It seemed to cope fine if I limited the query below 8 million rows. e.g (select * from tablename LIMIT 7000000).

thanks.

bcfletcher
Posts: 5
Joined: Thu 28 Jun 2007 03:34

Post by bcfletcher » Thu 05 Jul 2007 00:04

btw - I finally got this working using the Postgres ODBC driver v. 8.02.0400. It failed as well with an out of memory error at the transformation stage though. I managed to get it working by checking the "use Declare/Fetch" option in the ODBC driver settings. Is there an equivalent setting on the Core Lab driver?

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Thu 05 Jul 2007 10:35

Please provide me with CREATE TABLE statement, because I've got an error saying that data type for the column4 is invalid.

bcfletcher
Posts: 5
Joined: Thu 28 Jun 2007 03:34

Post by bcfletcher » Fri 06 Jul 2007 03:37

CREATE TABLE bigtable
(
column1 bigint NOT NULL,
column2 bigint,
column3 smallint,
column4 text,
column5 bigint,
column6 text,
column7 smallint,
column8 smallint,
column9 text,
column10 text,
column11 smallint,
column12 bigint,
CONSTRAINT column1_pk PRIMARY KEY (column1 )
)
WITH OIDS;

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Fri 06 Jul 2007 10:51

The problem seems not to pertain to our provider.
Could you describe your configuration? Do you have MS SQL Server, PostgreSQL server and MS SQL client all on one machine? If so, please try to find out which of three component raise the out of memory error.

bcfletcher
Posts: 5
Joined: Thu 28 Jun 2007 03:34

Post by bcfletcher » Tue 10 Jul 2007 00:32

Server 1 - Redhat ES4 Postgres 8.03
Server 2 - Windows 2003 Server, SQL 2005

Trying to Import using Server 2 connecting to Server 1 via the crlabs driver using the Data Import wizard in SQL 2005.

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Thu 12 Jul 2007 09:41

Server 1 - Redhat ES4 Postgres 8.03
Server 2 - Windows 2003 Server, SQL 2005
So which one has 2 Gig of RAM? Both?

Actually, I've reproduced this problem. Now we are trying to investigate it.

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Thu 12 Jul 2007 16:08

We have analyzed the problem and it seems to have nothing to do with PostgreSQLDirect .NET. I think that exception is raised by MS SQL Server. You should upload data onto it in chunks.

mnowill
Posts: 4
Joined: Wed 01 Oct 2008 16:59

Same prolem

Post by mnowill » Wed 01 Oct 2008 17:06

I am having the same issue. I am attempting to pull a table of 3 million rows via SSIS from Postgres into SQL. I am getting the same out of memory error. When looking into the issue I do not believe it is a SQL issue...if you watch the SSIS process in BIDS you will notice that the data reader task is not sending any rows through the pipeline. It appears to be waiting until the entire SQL statement is executed. Yet, if I limit the select to the top 10,000 I have no issue and can see the data being sent down the pipeline.

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Tue 07 Oct 2008 07:22

We are investigating this issue. You will be notified on the results as soon as possible.

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 08 Oct 2008 15:57

The problem is fixed. Look forward to the next build of PostgreSQLDirect .NET.

mnowill
Posts: 4
Joined: Wed 01 Oct 2008 16:59

Post by mnowill » Mon 20 Oct 2008 13:52

Great news...any idea on when the next build maybe released?

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Tue 21 Oct 2008 07:44

The new release of PostgreSQLDirect .NET 4.00 beta version will be available tomorrow.

The final version will be available in 1-2 weeks.

Post Reply