Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SSIS caching error Expand / Collapse
Author
Message
Posted Tuesday, May 6, 2014 10:53 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 12:27 PM
Points: 51, Visits: 83
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.
Post #1568093
Posted Tuesday, May 6, 2014 1:26 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 12:27 PM
Points: 51, Visits: 83
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?
Post #1568159
Posted Tuesday, May 6, 2014 1:53 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 18, 2014 10:12 AM
Points: 63, Visits: 365
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.



Post #1568171
Posted Tuesday, May 6, 2014 2:03 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 12:27 PM
Points: 51, Visits: 83
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?
Post #1568175
Posted Tuesday, May 6, 2014 2:49 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:49 PM
Points: 2,420, Visits: 6,736
Would it be an option to use raw files for the lookup?
Post #1568196
Posted Tuesday, May 6, 2014 2:49 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:55 AM
Points: 13,570, Visits: 11,383
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1568197
Posted Tuesday, May 6, 2014 2:58 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 12:27 PM
Points: 51, Visits: 83
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.
Post #1568199
Posted Tuesday, May 6, 2014 3:02 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 12:27 PM
Points: 51, Visits: 83
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.
Post #1568201
Posted Tuesday, May 6, 2014 3:03 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:55 AM
Points: 13,570, Visits: 11,383
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1568202
Posted Tuesday, May 6, 2014 3:10 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 12:27 PM
Points: 51, Visits: 83
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?
Post #1568205
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse