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

[CConnes]

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? ... sis#p28677

I'm using dcpostgresql65pro

Thanks
CConnes

[Shalex]

Thank you for your report. We will investigate the issue and notify you about the result as soon as possible.

[Shalex]

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.

[CConnes]

Thanks,

Is there a workaround to specify the FetchAll setting in the the DTS ?

[Shalex]

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]

New build of dotConnect for PostgreSQL 6.7.301 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/postgr ... nload.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.