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

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

Postby CConnes » Thu 11 Jul 2013 13:54

Hi,

I get on OutOfMemoryException while trying to migrate data from postgres to SqlServer 2008 with a big table (+8millions of rows):
The error message is :
- Pre-execute (Error)
Messages
Error 0xc0047062: Data Flow Task 1: System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown.
at Devart.Data.PostgreSql.PgSqlDataReader.e(Int32 A_0)
at Devart.Data.PostgreSql.PgSqlCommand.InternalExecute(CommandBehavior behavior, IDisposable stmt, Int32 startRecord, Int32 maxRecords)
at Devart.Common.DbCommandBase.InternalExecute(CommandBehavior behavior, IDisposable stmt, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
at Devart.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(IDTSManagedComponentWrapper100 wrapper) (SQL Server Import and Export Wizard)

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

Information 0x4004300b: Data Flow Task 1: "component "Destination - PMBATSTATEDET" (265)" wrote 0 rows.
(SQL Server Import and Export Wizard)


I've found a similar post in the forum in 2007 but the problem seems to come again :
http://forums.devart.com/viewtopic.php?f=3&t=10059&p=28677&hilit=ssis#p28677

I'm using dcpostgresql65pro

Thanks
CConnes
CConnes
 
Posts: 8
Joined: Fri 10 Feb 2012 14:51

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

Postby Shalex » Fri 12 Jul 2013 14:04

Thank you for your report. We will investigate the issue and notify you about the result as soon as possible.
Shalex
Devart Team
 
Posts: 7774
Joined: Thu 14 Aug 2008 12:44

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

Postby Shalex » Fri 26 Jul 2013 15:05

The Default FetchAll connection string parameter for defining default value of the FetchAll property of PgSqlCommand objects created on the connection is implemented. We will post here when the corresponding build of dotConnect for PostgreSQL is available for download.

The "Default FetchAll=false;" connection string setting with the upcoming build should fix the System.OutOfMemoryException problem.
Shalex
Devart Team
 
Posts: 7774
Joined: Thu 14 Aug 2008 12:44

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

Postby CConnes » Fri 02 Aug 2013 14:36

Thanks,

Is there a workaround to specify the FetchAll setting in the the DTS ?
CConnes
 
Posts: 8
Joined: Fri 10 Feb 2012 14:51

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

Postby Shalex » Tue 06 Aug 2013 07:15

We are going to release the new public build of dotConnect for PostgreSQL with the Default FetchAll connection string parameter this week. We will post here when it is available for download.
Shalex
Devart Team
 
Posts: 7774
Joined: Thu 14 Aug 2008 12:44

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

Postby Shalex » Thu 08 Aug 2013 15:47

New build of dotConnect for PostgreSQL 6.7.301 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/postgresql/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=3&t=27699.
Shalex
Devart Team
 
Posts: 7774
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for PostgreSQL