Hi,

Perhaps someone can give me some advice on an SSIS issue.

Ive created an SSIS process which contains among two for each loop containers to push multiple Excel files with multiple sheets into staging.

The process works well so far. But Ive experienced a large memory consumption while the data flows into the table. The memory usage grows constantly to memory limit. It appears while starting the package in SQL Server or Visual Studio 2012.

 

Here are some specs about the files:

File size: 50-90 MB

Amount: 39

Format: XLSX

Sheets: 4-5

Row count: 100000-360000

 

Machine Specs:

64 Bit OS

Windows Server 2012

SQL Server 2012 Enterprise

32GB RAM

 

Did someone experienced such issue?

Any advice?

Thanks in advance!

Donsk

• Edited by Donsk 18 hours 5 minutes ago

I suggest you read this:

https://technet.microsoft.com/library/Cc966529?f=255&MSPPError=-2147217396#EK

In my experience with Excel, it generally uses a good amount of memory to open any of its files.  What I also noticed with the FOREACH loop container when used with Excel files is that the Excel process may not be closed immediately at the end of the loop iteration.  This may be the reason why you are seeing your memory consumed while you are iterating through each Excel file.

If it has to be an Excel file, you may want to investigate 3rd party tools to open them such as Aspose or SpreadsheetGear.  Alternatively, you can write a script task to wait until the Excel file is no longer locked by SSIS process as with the link below.

http://microsoft-ssis.blogspot.com/2011/05/wait-until-file-is-unlocked.html

That experience was when I was working with SSIS 2008 R2, so it may have changed with the introduction of SSIS 2012.  Luckily, I have not needed to work with Excel via SSIS since then.

Hi Guys,

thanks for your answers!

@Tom: Thanks for the article which is very interessting! But in my case this fine tuning options concern more komplex processes (hope so). In my case I'm only push excel files through a  few components like derived columns and conditional split to reject null values. That's it. I've tested this with only one excel file (100 MB) which contains 6 sheets (250.000 rows each) and without the mentioned components. And i'm still get a high memory consumption.

When i open the excel file it consums 487 MB of memory. So, when i'm start the ssis process it consums nearly

6 sheets*487 MB = about 3GB of RAM every loop was made.

@Jimbo

I think you are right. I mean it looks like this.

Your link is interessting, too. But in this case i would need sth. that e.g. kill the excel process/release the memory from it every loop was made?

Or I'm wrong?

Thanks your help guys!

When I looped through the Excel files in SSIS years ago, I generally just added a task to wait until the Excel has been released by SSIS.  By releasing the Excel process, the resources used to open the Excel file were released back to the OS.  I processed several Excel files in this way, and the memory consumption was in line with expectation of each Excel file and SSIS overhead.