Redshift connector forces SSIS to stop

More
5 years 2 months ago #10628 by danielbos
danielbos created the topic: Redshift connector forces SSIS to stop
Hi all,

I use the Redshift connector. Since I am testing the connector right now with Redshift I created a simple package which get data from table A (Redshift) and insert it into table B (also Redshift).


My environment:
Fresh install of:
    Windows Server 2012
    SQL Server 2012 Standard[/list:u]

    The settings I use for the PGNP driver are:
    Fetch Rows: 100000
    Bulk Insert Method: Pipe/Copy
    Bulk Insert Buffer Size: 100000
    AWS keys and such are filled in in order to use S3 for the copy operation.[/list:u]

    After about 4 milion records the SSIS package fails and I have no idea how to solve this.

    The errors that I could find in the Eventlog:

File Attachment:

File Name: 474_28fe33...e227.jpg
File Size:102 KB
Application: DtExecUI.exe
Framework Version: v4.0.30319
Description: The process was terminated due to an unhandled exception.
Exception Info: exception code c0000005, exception address 5C229DD3
Stack:

And:
<!-- ia0 -->ScreenHunter_21 Dec. 05 08.31.gif<!-- ia0 -->
Faulting application name: DtExecUI.exe, version: 11.0.3368.0, time stamp: 0x519d5ea7
Faulting module name: PGNP.dll, version: 1.4.0.3196, time stamp: 0x52788c8b
Exception code: 0xc0000005
Fault offset: 0x000c9dd3
Faulting process id: 0x1364
Faulting application start time: 0x01cef188fa510d8e
Faulting application path: C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\DtExecUI.exe
Faulting module path: C:\Program Files (x86)\Intellisoft\RSNP\PGNP.dll
Report Id: a74823df-5d7c-11e3-9439-22000a24224f
Faulting package full name: 
Faulting package-relative application ID:

Does anybody know how to solve this?

Kind regards,

Daniel
Attachments:

Please Log in or Create an account to join the conversation.

More
5 years 2 months ago #10630 by Moderator
Moderator replied the topic: Re: Redshift connector forces SSIS to stop
Hi Daniel,
could you please send us the table DDL and the test package? We will try to reproduce and fix the issue.

Also, have you used FastLoad in the destination?

Thank you
Moderator

Please Log in or Create an account to join the conversation.

More
5 years 2 months ago #10656 by danielbos
danielbos replied the topic: Re: Redshift connector forces SSIS to stop
Hi PGNP,

I have two simple tables, like:
CREATE TABLE public.a (
  b  varchar(256)
) ;

CREATE TABLE public.b_test (
  b  varchar(256)
) ;

I didn't use the fastload option.


When I do, the performance drops significantly (without setting the number of rows).
<!-- ia0 -->ScreenHunter_24 Dec. 06 08.34.gif<!-- ia0 -->

When I set also the number of rows to insert in a batch, like below, I get an Out of memory error.

File Attachment:

File Name: 474_28fe33...e227.jpg
File Size:102 KB
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:

Error at Data Flow Task &#91;OLE DB Source &#91;26&#93;&#93;: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available.  Source: "Redshift OLE DB Provider"  Hresult: 0x80040E14  Description: "out of memory for query result
".

Error at Data Flow Task &#91;OLE DB Source &#91;26&#93;&#93;: Opening a rowset for ""public"."b_test"" failed. Check that the object exists in the database.
Error at Data Flow Task &#91;SSIS.Pipeline&#93;: "OLE DB Source" failed validation and returned validation status "VS_ISBROKEN".
Error at Data Flow Task &#91;SSIS.Pipeline&#93;: One or more component failed validation.
Error at Data Flow Task: There were errors during task validation.

 (Microsoft.DataTransformationServices.VsIntegration)

------------------------------
BUTTONS:
OK
------------------------------

I have send you the DDL and package. Thank you for looking into it!

Daniel
Attachments:

Please Log in or Create an account to join the conversation.

More
5 years 1 month ago #10734 by Moderator
Moderator replied the topic: Re: Redshift connector forces SSIS to stop
We are investigating the issue. As first step, we enabled using Redshift cursors in build 3206.

As recommendation, could you set "Rows per batch" to a smaller number, say 1000, and see if that resolves the out-of-memory issue.

Please Log in or Create an account to join the conversation.

More
5 years 1 month ago #10738 by moodu
moodu replied the topic: Re: Redshift connector forces SSIS to stop
I had a similar issue. The pagefile for virtual memory was set to manual and it had a hard cap. I had more luck when i set it back to system managed. Worth a check.

Please Log in or Create an account to join the conversation.

More
5 years 1 week ago #10825 by Moderator
Moderator replied the topic: Re: Redshift connector forces SSIS to stop
I have performed various profiling tests, and confirmed that there are neither memory leaks, nor memory exceptions generated by the provider.

Then I found the following article: http://social.msdn.microsoft.com/Forums ... onservices . The article describes same issue.

Then I increased DefaultBufferSize to 20485760 (in the Control Flow Properties). And it solved the issue! The test package was able to continue processing beyond 10 million rows. I think this issue was caused by the way the SSIS manages internal buffers.

Also, the package can be changed to use queries with LIMIT/OFFSET clauses to be able to process any number of rows (many billions).

Please Log in or Create an account to join the conversation.

Time to create page: 0.285 seconds
Powered by Kunena Forum