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 Friday, September 11, 2015 12:58 PM

Hi Guys,

This solution works so far. But I have an issue with using the VB.net code from your link above.

I've adjusted the code to recieve the variable value (FilePath) from the For each loop container. So far so good!

The script is placed in the 2nd For each loop container which contains the data flow task.

• 1st Container is to recieve multiple (dynamic) excel files
• 2nd Container is to recieve sheetnames from the excel files

Example:

http://udayarumilli.com/passing-variables-at-runtime-ssis-dynamic-execution-of-ssis-packages/

When I set breakpoints in the script and start them manually it works. I 've repeated this three times (for three sheets). The memory always decrease after every load. Very cool!

But when I'm start the whole container i get the message that the file is locked by another process. Thereby the script doesn't finish.

Can you help me with this?

Thanks!

Donsk

• Edited by Donsk 14 hours 36 minutes ago