Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

I have around 6+ years of experience in SQL Server development and in Business Intelligence area. I am working as Datawarehouse developer. My blog space includes all technical areas related to SQL Server and MSBI (SSIS, SSRS and SSAS).
Browse by Tags · View All
SSIS 7
xml 3
dynamic 3
Union All 3
SSRS 2
Data flow task 2
Buffer 2
Time 2
Date 2
varchar(max) 1

Archive · View All
February 2013 3
September 2009 3
June 2013 2
January 2013 2
November 2012 2
July 2012 2
July 2009 2
April 2013 1
December 2012 1
February 2010 1

SSIS Buffer error

Jan 7 2013 12:00AM by Divya Agrawal Shah   

Recently, while I was working on SSIS packages, I encountered an error  “ A buffer failed while allocating .. bytes”. Entire package was working perfectly when the number of rows transferred were optimal. However, as soon as the package started loading millions of rows, there was a sudden error complaining about buffer.

image

When I went into the depth of the error, the error was due to the “UNION ALL” used in the Data Flow task. The error was “[-1073450974] SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Union All .." (541) failed with error code 0x8007000E while processing input "Union All Input " (615). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. “

image

 

The package I was trying to execute was a Fact package, where in the Data Flow task, I have used various Lookup Transformations to fetch the Surrogate key from the Dimensions. This is a routine process in a DFT, where we try to get the Surrogate Key for Dimension and if its not available, we store it as an “Unknown” value. This require, Lookup Transformation, followed by a Derived column transformation and then finally Merging both the outputs via Union All transformation.

image

Now, here was the issue,  basically, each column in a DFT is identified by a lineageid starting from the Source. When this column is passed through Union all, it changes the lineageid of the column and treats it as new column. Hence, every time a column is passed via Union All task, it will generate a new lineageid and a new buffer for it. This requires more space as compared to the column which is directly passed from source to destination. The aid for this issue is when a package is aimed to load huge volume of data, try to avoid use of Union All.

 

Instead of using Union All to merge, you can set the Lookup Transformation “ Specify how to handle rows with no matching entries” property to “IGNORE FAILURE”

image

Specifying this property, will always send every row as “Match Output”, with the rows with “no match entries with NULL”. Connect the Derived Column Transformation from the Lookup with “Match output”.

Inside the Derived Column Transformation, check for the Key value, if it is NULL , then take Unknown else the same key.

image

image

Please let me know your feedback on this problem. If anyone has encountered such error, let me the work around used to handle it.

Tags: SSIS, Buffer, Error, Union All, Lineageid, Data flow task


Divya Agrawal Shah
89 · 2% · 629
4
 
0
Lifesaver
 
 
0
Learned
 
0
Incorrect



Submit

7  Comments  

  • Hi Divya

    I haven't come across this before but it's good that you did. There is no need for the UNION ALL executable here so your 'workaround' resulted in a more efficient design.

    As you alluded to, UNION ALL is a partially blocking transformation i.e. its output set may differ in length to its input set. Because of this, UNION ALL creates new buffers for its output and copies the required data into them from the input before passing the buffers downstream. The copying of buffers slows down the package as well as making it more memory-hungry. All this you know already...

    Have you monitored package execution with PerfMon? How far does the package get before it crashes? How much memory is being consumed by the package?

    Regards Andy

    commented on Jan 7 2013 4:06AM
    a.diniz
    316 · 0% · 139
  • The package loaded half of the rows out of 30 Million rows. The package has around 5 Union All tasks. However, after removing the Union All the package executed very fast.

    commented on Jan 7 2013 4:23AM
    Divya Agrawal Shah
    89 · 2% · 629
  • Which version of SSIS are you running? If 2008 or above, I'd be interested to know how much memory has been granted to SSIS and how much the package is consuming. You said that your package originally had 5 Union All transforms. Does it also have 5 Lookups? If so, are they large? Are they cached?

    commented on Jan 8 2013 2:56AM
    a.diniz
    316 · 0% · 139
  • I dont have access to my production server where the package is executing, so cant say about memory. And, yes the package had 5 lookups, for each lookup we had a Union all task. The lookups were Full Cached and fairly huge.

    commented on Jan 8 2013 8:03AM
    Divya Agrawal Shah
    89 · 2% · 629
  • Hi Divya,

    Its very informative and thanks for sharing your experience with us.

    while explaining the problem with union all transformation, try to explain the functionality of union all transformation in ssis, so that it will reach easily to the end users or readers especially beginners.

    Thanks again for sharing your thoughts.

    commented on Jan 10 2013 4:27AM
    Bala Krishna
    84 · 2% · 676
  • Thanks balakrishna. I will keep in mind your suggestion to explain about how Union All works, i will try to post a blog post on it and link with this post.

    commented on Jan 10 2013 10:47PM
    Divya Agrawal Shah
    89 · 2% · 629
  • This comment is waiting for moderation.

    commented on Aug 22 2016 9:21AM
    tusharp457
    3158 · 0% · 2

Your Comment


Sign Up or Login to post a comment.

"SSIS Buffer error" rated 5 out of 5 by 4 readers
SSIS Buffer error , 5.0 out of 5 based on 4 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]