Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS caching error


SSIS caching error

Author
Message
skaggs.andrew
skaggs.andrew
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 93
I am running a pretty straight forward dataflow that is:

Source -> Lookup -> Destination

I am in the process of trying to test run an incremental load. When I have no checked columns coming from the lookup table, this process is successful in roughly 5 mins (4 mill records cached).

Now I need to check some of the columns in the lookup so I can push them out to a conditional split to check for changes in the source data. When I check some of the columns in the lookup and rerun this process, it appears that it is caching all of the records, but then the package fails with the following errors:

Error: A buffer failed while allocating 10458336 bytes.

Error: The system reports 96 percent memory load. There are 17179336704 bytes of physical memory with 526725120 bytes free. There are 4294836224 bytes of virtual memory with 159760384 bytes free. The paging file has 34356768768 bytes with 14195691520 bytes free.

[SSIS.Pipeline] Error: The Data Flow task failed to create a buffer to call PrimeOutput for output "HZ_SCORE_EXPORT" (1) on component "OLE DB Source Output" (12). This error usually occurs due to an out-of-memory condition.


Based on this, I am assuming that it's a RAM issue? Please let me know if that is not the case and if so what might be the error.

Currently, this server has 16GB of RAM. If I need to add more, how much should I add?

Thanks for the help.
skaggs.andrew
skaggs.andrew
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 93
I just added 8 GB of RAM, reran my dataflow and it still error'd with the same message, so I guess the RAM is not the true issue. Any thoughts?
DouglasH
DouglasH
SSC-Enthusiastic
SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)

Group: General Forum Members
Points: 125 Visits: 1469
I don't really have a answer for you but I've seen this sort of thing on the forum before. The consensus seems to be that it's not a pure RAM issue but a SSIS package architecture/configuration issue.

Can you alter the package to process the data in chunks? Can you try a SQL Server provider rather than OLEDB?
Have you enabled logging in the package to get more detailed messages?

Maybe someone else can help more.



skaggs.andrew
skaggs.andrew
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 93
Thanks for the response DouglasH. The lookup is the destination table for this workflow. Not really sure how to redesign this mapping to use multiple lookups to achieve the same result. It seems kinda weird to me that this is failing because the records that are being cached are not large in width and there are roughly 15 columns that I am caching. This just seems really odd.

Any other suggestions?
Eirikur Eiriksson
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6780 Visits: 17739
Would it be an option to use raw files for the lookup?
Cool
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16469 Visits: 13207
skaggs.andrew (5/6/2014)
Thanks for the response DouglasH. The lookup is the destination table for this workflow. Not really sure how to redesign this mapping to use multiple lookups to achieve the same result. It seems kinda weird to me that this is failing because the records that are being cached are not large in width and there are roughly 15 columns that I am caching. This just seems really odd.

Any other suggestions?


Why are you caching 15 columns? Do you need them all to do the lookup?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
skaggs.andrew
skaggs.andrew
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 93
This process is going to be used on a nightly basis. Not sure how I could use a raw file unless I had a process that created a new file for me in a directory, then used that file for the lookup. How would using a flat file help? Sorry, I am unfamiliar with using flat files in a lookup.
skaggs.andrew
skaggs.andrew
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 93
Koen Verbeeck (5/6/2014)
skaggs.andrew (5/6/2014)
Thanks for the response DouglasH. The lookup is the destination table for this workflow. Not really sure how to redesign this mapping to use multiple lookups to achieve the same result. It seems kinda weird to me that this is failing because the records that are being cached are not large in width and there are roughly 15 columns that I am caching. This just seems really odd.

Any other suggestions?


Why are you caching 15 columns? Do you need them all to do the lookup?



I am caching these 15 columns because if there is a match (joining on 3 columns) I want these records to continue to a conditional split transformation. In there I will check each column to see if the column value from the source matches the column value from the target. This is where I can determine what records need updated in the target table.

In the caching I am also already using a where clause to reduce any unnecessary records. The lookup only contains the records/columns that I need.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16469 Visits: 13207
skaggs.andrew (5/6/2014)
This process is going to be used on a nightly basis. Not sure how I could use a raw file unless I had a process that created a new file for me in a directory, then used that file for the lookup. How would using a flat file help? Sorry, I am unfamiliar with using flat files in a lookup.


When you want to use a flat file as a cache, you need to use the cache connection manager.
This cache needs to be created be a seperate process.

Lookup - Using the cache connection manager

By the way, this cache is NOT the same as an SSIS RAW file.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
skaggs.andrew
skaggs.andrew
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 93
Koen Verbeeck (5/6/2014)
skaggs.andrew (5/6/2014)
This process is going to be used on a nightly basis. Not sure how I could use a raw file unless I had a process that created a new file for me in a directory, then used that file for the lookup. How would using a flat file help? Sorry, I am unfamiliar with using flat files in a lookup.


When you want to use a flat file as a cache, you need to use the cache connection manager.
This cache needs to be created be a seperate process.

Lookup - Using the cache connection manager

By the way, this cache is NOT the same as an SSIS RAW file.



Do you think this is the route that I should be going for this dataflow? Is what I am attempting to do now not suitable for SSIS?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search