none
Strange Error messages running SSIS package RRS feed

  • Question

  • This package which is a child package has been running successfully for quite some time now. All of a sudden we are getting these intermittant error messages. Does anyone have any ideas what to do or check for?

    thanks

    ===========================

    Error portion

    Error: 0xC0047012 at CF-DFT Oracle Sales Fact, DTS.Pipeline: A buffer failed while allocating 100483760 bytes.

    Error: 0xC02020C4 at CF-DFT Oracle Sales Fact, order line id [1]: The attempt to add a row to the Data Flow task buffer failed with error code 0x8007000E.

    Error: 0xC0047011 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The system reports 30 percent memory load. There are 8587960320 bytes of physical memory with 5972680704 bytes free. There are 2147352576 bytes of virtual memory with 1324290048 bytes free. The paging file has 12673945600 bytes with 10005012480 bytes free.

    Error: 0xC0047038 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The PrimeOutput method on component "order line id" (1) 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.

    Error: 0xC0047056 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The Data Flow task failed to create a buffer to call PrimeOutput for output "Union All" (13359) on component "Union All Output 1" (13361). This error usually occurs due to an out-of-memory condition.

    Error: 0xC0047021 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Thread "SourceThread1" has exited with error code 0xC0047038.

    Error: 0xC0047021 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Thread "WorkThread2" has exited with error code 0x8007000E.

    Error: 0xC0047039 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Thread "WorkThread3" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

    Error: 0xC0047039 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

    Error: 0xC0047021 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Thread "WorkThread3" has exited with error code 0xC0047039.

    Error: 0xC0047021 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Thread "WorkThread1" has exited with error code 0xC0047039.

    Error: 0xC0047039 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

    Error: 0xC0047021 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.

    ====================================================

    Complete child package log

    Executing ExecutePackageTask: D:\ssis\srw\packages\SRW_ORACLE_SALES_FTBL.dtsx

    Information: 0x40016041 at SRW_ORACLE_SALES_FTBL: The package is attempting to configure from the XML file "D:\SSIS\configuration\CONFIG-STAGE1.dtsConfig".

    Information: 0x40016040 at SRW_ORACLE_SALES_FTBL: The package is attempting to configure from SQL Server using the configuration string ""MSSQL-CONFIG";"[dbo].[SSIS_Configurations]";"System Configuration Settings";".

    Information: 0x40016040 at SRW_ORACLE_SALES_FTBL: The package is attempting to configure from SQL Server using the configuration string ""MSSQL-CONFIG";"[dbo].[SRW_SSIS_Configurations]";"SRW Main Configurations";".

    Information: 0x4004300A at CF-DFT Oracle Sales Fact, DTS.Pipeline: Validation phase is beginning.

    Warning: 0x802092A7 at CF-DFT Oracle Sales Fact, TEMP OUTPUT [998]: Truncation may occur due to inserting data from data flow column "IC_ORDER" with a length of 240 to database column "IC_ORDER" with a length of 1.

    Warning: 0x80047076 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The output column "SERIAL_NUMBER" (2680) on output "Sort Output" (2453) and component "Sort 1" (2451) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.

    Warning: 0x80047076 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The output column "ORG_ID" (13377) on output "Union All Output 1" (13361) and component "Union All" (13359) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.

    Warning: 0x80047076 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The output column "CUST_TRX_TYPE_ID" (13428) on output "Union All Output 1" (13361) and component "Union All" (13359) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.

    Warning: 0x80047076 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The output column "Data Conversion 1.Copy of CUST_TRX_TYPE_ID" (13443) on output "Union All Output 1" (13361) and component "Union All" (13359) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.

    Warning: 0x80047076 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The output column "GL_ID_REV" (13449) on output "Union All Output 1" (13361) and component "Union All" (13359) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.

    Warning: 0x80047076 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The output column "Copy of GL_ID_REV" (13458) on output "Union All Output 1" (13361) and component "Union All" (13359) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.

    Information: 0x4004300A at CF-DFT Oracle Sales Fact, DTS.Pipeline: Validation phase is beginning.

    Warning: 0x802092A7 at CF-DFT Oracle Sales Fact, TEMP OUTPUT [998]: Truncation may occur due to inserting data from data flow column "IC_ORDER" with a length of 240 to database column "IC_ORDER" with a length of 1.

    Warning: 0x80047076 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The output column "SERIAL_NUMBER" (2680) on output "Sort Output" (2453) and component "Sort 1" (2451) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.

    Warning: 0x80047076 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The output column "ORG_ID" (13377) on output "Union All Output 1" (13361) and component "Union All" (13359) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.

    Warning: 0x80047076 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The output column "CUST_TRX_TYPE_ID" (13428) on output "Union All Output 1" (13361) and component "Union All" (13359) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.

    Warning: 0x80047076 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The output column "Data Conversion 1.Copy of CUST_TRX_TYPE_ID" (13443) on output "Union All Output 1" (13361) and component "Union All" (13359) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.

    Warning: 0x80047076 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The output column "GL_ID_REV" (13449) on output "Union All Output 1" (13361) and component "Union All" (13359) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.

    Warning: 0x80047076 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The output column "Copy of GL_ID_REV" (13458) on output "Union All Output 1" (13361) and component "Union All" (13359) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.

    Information: 0x4004300A at CF-DFT Oracle Sales Fact, DTS.Pipeline: Validation phase is beginning.

    Warning: 0x802092A7 at CF-DFT Oracle Sales Fact, TEMP OUTPUT [998]: Truncation may occur due to inserting data from data flow column "IC_ORDER" with a length of 240 to database column "IC_ORDER" with a length of 1.

    Warning: 0x80047076 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The output column "SERIAL_NUMBER" (2680) on output "Sort Output" (2453) and component "Sort 1" (2451) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.

    Warning: 0x80047076 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The output column "ORG_ID" (13377) on output "Union All Output 1" (13361) and component "Union All" (13359) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.

    Warning: 0x80047076 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The output column "CUST_TRX_TYPE_ID" (13428) on output "Union All Output 1" (13361) and component "Union All" (13359) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.

    Warning: 0x80047076 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The output column "Data Conversion 1.Copy of CUST_TRX_TYPE_ID" (13443) on output "Union All Output 1" (13361) and component "Union All" (13359) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.

    Warning: 0x80047076 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The output column "GL_ID_REV" (13449) on output "Union All Output 1" (13361) and component "Union All" (13359) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.

    Warning: 0x80047076 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The output column "Copy of GL_ID_REV" (13458) on output "Union All Output 1" (13361) and component "Union All" (13359) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.

    Information: 0x40043006 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Prepare for Execute phase is beginning.

    Information: 0x40043007 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Pre-Execute phase is beginning.

    Information: 0x400490F4 at CF-DFT Oracle Sales Fact, REV GL SEGS [307]: component "REV GL SEGS" (307) has cached 780 rows.

    Information: 0x400490F4 at CF-DFT Oracle Sales Fact, get oper unit [813]: component "get oper unit" (813) has cached 12 rows.

    Warning: 0x802090E4 at CF-DFT Oracle Sales Fact, get oper unit [813]: The Lookup transformation encountered duplicate reference key values when caching reference data. The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.

    Information: 0x400490F4 at CF-DFT Oracle Sales Fact, get header txn type for IC flag [13685]: component "get header txn type for IC flag" (13685) has cached 768 rows.

    Information: 0x4004300C at CF-DFT Oracle Sales Fact, DTS.Pipeline: Execute phase is beginning.

    Information: 0x4004800D at CF-DFT Oracle Sales Fact, DTS.Pipeline: The buffer manager failed a memory allocation call for 100484768 bytes, but was unable to swap out any buffers to relieve memory pressure. 83 buffers were considered and 83 were locked. Either not enough memory is available to the pipeline because not enough are installed, other processes were using it, or too many buffers are locked.

    Error: 0xC0047012 at CF-DFT Oracle Sales Fact, DTS.Pipeline: A buffer failed while allocating 100484768 bytes.

    Error: 0xC0047011 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The system reports 31 percent memory load. There are 8587960320 bytes of physical memory with 5869387776 bytes free. There are 2147352576 bytes of virtual memory with 1223802880 bytes free. The paging file has 12673945600 bytes with 9901600768 bytes free.

    Information: 0x4004800D at CF-DFT Oracle Sales Fact, DTS.Pipeline: The buffer manager failed a memory allocation call for 100483760 bytes, but was unable to swap out any buffers to relieve memory pressure. 162 buffers were considered and 162 were locked. Either not enough memory is available to the pipeline because not enough are installed, other processes were using it, or too many buffers are locked.

    Error: 0xC0047012 at CF-DFT Oracle Sales Fact, DTS.Pipeline: A buffer failed while allocating 100483760 bytes.

    Error: 0xC02020C4 at CF-DFT Oracle Sales Fact, order line id [1]: The attempt to add a row to the Data Flow task buffer failed with error code 0x8007000E.

    Error: 0xC0047011 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The system reports 30 percent memory load. There are 8587960320 bytes of physical memory with 5972680704 bytes free. There are 2147352576 bytes of virtual memory with 1324290048 bytes free. The paging file has 12673945600 bytes with 10005012480 bytes free.

    Error: 0xC0047038 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The PrimeOutput method on component "order line id" (1) 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.

    Error: 0xC0047056 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The Data Flow task failed to create a buffer to call PrimeOutput for output "Union All" (13359) on component "Union All Output 1" (13361). This error usually occurs due to an out-of-memory condition.

    Error: 0xC0047021 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Thread "SourceThread1" has exited with error code 0xC0047038.

    Error: 0xC0047021 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Thread "WorkThread2" has exited with error code 0x8007000E.

    Error: 0xC0047039 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Thread "WorkThread3" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

    Error: 0xC0047039 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

    Error: 0xC0047021 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Thread "WorkThread3" has exited with error code 0xC0047039.

    Error: 0xC0047021 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Thread "WorkThread1" has exited with error code 0xC0047039.

    Error: 0xC0047039 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

    Error: 0xC0047021 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.

    Information: 0x40043008 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Post Execute phase is beginning.

    Information: 0x402090DF at CF-DFT Oracle Sales Fact, TEMP OUTPUT [998]: The final commit for the data insertion has started.

    Information: 0x402090E0 at CF-DFT Oracle Sales Fact, TEMP OUTPUT [998]: The final commit for the data insertion has ended.

    Information: 0x40043009 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Cleanup phase is beginning.

    Information: 0x4004300B at CF-DFT Oracle Sales Fact, DTS.Pipeline: "component "TEMP OUTPUT" (998)" wrote 0 rows.

    Task failed: CF-DFT Oracle Sales Fact

    Warning: 0x80019002 at SRW_ORACLE_SALES_FTBL: The Execution method succeeded, but the number of errors raised (15) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

    Task failed: CF-EPGT SRW_ORACLE_SALES_FTBL

    Warning: 0x80019002 at CF-SQC Facts: The Execution method succeeded, but the number of errors raised (15) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

    Warning: 0x80019002 at SRW_MAIN: The Execution method succeeded, but the number of errors raised (15) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

    SSIS package "SRW_Main.dtsx" finished: Failure.

    Thursday, May 18, 2006 11:31 PM

Answers

  • Most probably the SSIS process has run out of virtual memory.

    Apparently, there is plenty of physical memory on this machine (8Gb?), but the amount of virtual memory a single process can use is usually 2Gb, and it seems like there is no more space for 100Mb buffer that package tries to allocate.

    Without knowing the package content, it is hard to give any advice how to fix it. But some possible ideas:

    1. I suspect you've increased default buffer size, set it back to ~10Mb default
    2. Switch to 64-bit OS (pretty radical, is not it?)
    3. Add /3Gb switch to boot.ini
    4. Split the package (if it has multiple data flows) into sub-packages and execute them with out-of-process option in Execute Package Task (so that each sub-package gets its own VM space)
    5. Install SP1 - although I don't remember if there were any specific changes in memory handling
    Friday, May 19, 2006 1:13 AM
  • Using parent/child pattern in this case is a good idea. Note that by default, Execute Package task executes child packages in the same process as parent packages. You may get more (total for all packages) usable memory on 32-bit machines if you change it to execute child packages in a separate processes.
    Friday, May 19, 2006 5:54 AM

All replies

  • Most probably the SSIS process has run out of virtual memory.

    Apparently, there is plenty of physical memory on this machine (8Gb?), but the amount of virtual memory a single process can use is usually 2Gb, and it seems like there is no more space for 100Mb buffer that package tries to allocate.

    Without knowing the package content, it is hard to give any advice how to fix it. But some possible ideas:

    1. I suspect you've increased default buffer size, set it back to ~10Mb default
    2. Switch to 64-bit OS (pretty radical, is not it?)
    3. Add /3Gb switch to boot.ini
    4. Split the package (if it has multiple data flows) into sub-packages and execute them with out-of-process option in Execute Package Task (so that each sub-package gets its own VM space)
    5. Install SP1 - although I don't remember if there were any specific changes in memory handling
    Friday, May 19, 2006 1:13 AM
  • Thanks for the information. I'll review this when I get back to work. We currently design all our ETLs to be designed in a parent/child pattern where there is one driver that calls all the child packages.

    We can't change/convert to 64bit just yet but it's good to know that it's a plausible option in the future. I'll have to check on the /3gb option. I know we haven't done that in this case. SP1 didn't appear to have much of value for us so we haven't even considered it. However, this might be a reason to take a more detailed look at it. I'll check on the buffer size. I just don't remember if it was changed. However, I do suspect it was increased to try to get better performance.

    John

    Friday, May 19, 2006 2:08 AM
  • Using parent/child pattern in this case is a good idea. Note that by default, Execute Package task executes child packages in the same process as parent packages. You may get more (total for all packages) usable memory on 32-bit machines if you change it to execute child packages in a separate processes.
    Friday, May 19, 2006 5:54 AM
  • I made the change to have the child packages run in their own memory context and it appears to be working for now. I'll keep my fingers crossed.

    thanks for the help

    John

    Friday, May 19, 2006 8:30 PM
  • In my specific case, simply changing the sample size in the "row sampling transformation" which was part of the package sorted this.?
    It seems I could sample 20Million rows, but cannot do 50mill.

     

    Wednesday, April 28, 2010 4:39 PM
  • Experiencing same issue, but solved with the help of your bellow comment

    "I suspect you've increased default buffer size, set it back to ~10Mb default "

     

    V-Jai

    Thursday, September 22, 2011 9:31 AM
  • VJai,

    how did you decrease the buffer to ~10Mb default

    i have data flow task component which comprises of a oledb source and a scrip component. The vb code for the script component is as below

    Public Overrides Sub PreExecute()
            MyBase.PreExecute()
            '
            ' Add your code here for preprocessing or remove if not needed
            '
        End Sub

    Public Overrides Sub ProcessInput(ByVal InputID As Integer, ByVal Buffer As PipelineBuffer)

            While (Buffer.NextRow())
                Dim newFileName As String
                Dim destFilePath As String
                Dim destFileName As String
                Dim delimiter As String
                Dim indx As Integer

               
                newFileName = Path.GetRandomFileName()
                destFilePath = "dir\struc"
               
                destFileName = Path.Combine(destFilePath, newFileName)

                writeFile = New FileStream(destFileName & ".dat", FileMode.Create, FileAccess.Write)
                sw = New StreamWriter(writeFile)


                delimiter = Buffer(1).ToString()
                sw.Write(Buffer(0).ToString() & delimiter)

                For indx = 2 To Buffer.ColumnCount - 1
                   
                        sw.Write(vbCrLf)
                        sw.Write(Buffer(indx).ToString() & delimiter)


                    Else
                        If (Buffer.IsNull(indx)) Then
                            sw.Write(delimiter)
                        Else
                            sw.Write(Buffer(indx).ToString() & delimiter)
                        End If

                    End If
                Next

                sw.Close()
            End While
            Buffer.Dispose()


        End Sub

     

    Public Overrides Sub PostExecute()
            MyBase.PostExecute()
            sw.Close()
            writeFile.Close()
        End Sub

    i get the belwo warnings everytime i run the package for all the output values, the problem is i cant remove these output as my script task is consuming them.

    Warning: 2011-10-21 14:20:13.55

       Code: 0x80047076

       Source: Data Flow Task DTS.Pipeline

       Description: The output column "CLM_03" (2139) on output "OLE DB Source Output" (11) and component "OLE DB Source" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.

    End Warning

    Any help would be much appreciated.

    Thanks in advance

     

    • Edited by r_ean Friday, October 21, 2011 9:14 PM
    Friday, October 21, 2011 9:00 PM
  • try this

    provide blobtempstoragepath and buffertempstoragepath for Dataflow task

    Thursday, September 12, 2019 9:14 AM