The Execute method in Microsoft.SqlServer.Dts.RunTime.Package class has memory leak after each invokation. This following code demonstrates it.
Output from the program:
Allocated memory after 1 iteration(s) = 476316
Allocated memory after 2 iteration(s) = 546448
Allocated memory after 3 iteration(s) = 555008
Allocated memory after 4 iteration(s) = 563632
Allocated memory after 5 iteration(s) = 572232
Allocated memory after 6 iteration(s) = 580856
Allocated memory after 7 iteration(s) = 589480
Allocated memory after 8 iteration(s) = 598240
Allocated memory after 9 iteration(s) = 606816
Allocated memory after 10 iteration(s) = 615424
Allocated memory after 11 iteration(s) = 624000
Allocated memory after 12 iteration(s) = 632576
Allocated memory after 13 iteration(s) = 641152
Allocated memory after 14 iteration(s) = 649728
Allocated memory after 15 iteration(s) = 658352
Allocated memory after 16 iteration(s) = 666948
Allocated memory after 17 iteration(s) = 675760
Allocated memory after 18 iteration(s) = 684380
Allocated memory after 19 iteration(s) = 693008
Allocated memory after 20 iteration(s) = 701532
//--
// The Execute method in Microsoft.SqlServer.Dts.RunTime.Package has memory
// leak. This program demonstrates it. The package invoked by this program has
// only a single 'Script Task' that does nothing.
//
// To compile, add referece to Microsoft.SQLServer.ManagedDTS.dll.
//
// csc /r:"C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll" ExecPackage.cs
//
//--
using System;
using System.Diagnostics;
using Microsoft.SqlServer.Dts.Runtime;
namespace Misc
{
/// <summary>
/// Programmatically executes SSIS package, then displays memeory usage
/// after each execution. The memeory usage goes up after each
/// Package.Execute() call, which indicates memory leak!
/// </summary>
static class ExecPackage
{
static void DisplayUsage()
{
Console.WriteLine(@."Usage: ExecPackage <pkgName>");
Console.WriteLine(@." Package <pkgName> resides in Package Store on localhost under \File System\");
}
static void Main(string[] args)
{
// Parse command line arguments.
if (args.Length != 1)
{
DisplayUsage();
return;
}
string pkgName = @."\File System\" + args[0];
// Programmatically execute the package several times.
Application app = new Application();
for (int i = 1; i <= 20; i++)
{
Package pkg = app.LoadFromDtsServer(pkgName, "localhost", null);
pkg.Execute(); // comment out this line, then allocated memory does not increase
// Process.Start("dtexec.exe", "/dts \"" + pkgName + "\"");
pkg.Dispose();
pkg = null;
// Do garbage collection, then display memory usage
GC.Collect();
Console.WriteLine("Allocated memory after {0} iteration(s) = {1}",
i, GC.GetTotalMemory(true));
}
}
}
}
Thank you for the detailed posting and your time. I have gathered the information and we will investigate.
|||Craig,
Are you able to confirm the issue? Is there a fix or workaround?
Thanks,
Bin
|||Bin, yes we are able to reproduce the issue and we are reviewing it for inclusion into the next service pack. However at this time there is not real work around other than restarting the appliction that calls the package. Thank you again for your time and patience.
|||Has there been any updates with this issue? I am currently dealing with the same problem.
Thanks,
Drew
|||the next service pack would be sp2, which has not yet been released.|||Thanks, but... is this really such a low priority it doesn't justify a hotfix? I'd say not being able to execut SSIS packages programmatically without having to write hacked code would be up on the top of their list... I know they had released a hotfix for the fuzzy lookup memory leak in the past... Is there anyone from MS who can confirm this is true?dferraro wrote:
Has there been any updates with this issue? I am currently dealing with the same problem.
Thanks,
Drew
Thanks again,
Drew|||Please advise...... we are looking to use this in a production environment, and I would like to know if/when this issue is fixed, so I can have better knowledge when deciding which options to pursue. How are other people dealing with this issue? Using dtexec? But what if you need to check return values, etc? Any other work-arounds besides building a composite app that runs the package and then dies off?
Thanks again,
Drew|||Looks like I'm having the same problem as well. I've got a .Net app that dynamically builds 120 tasks in a package and pulls in 3 million records running once an hour. I thought I had a memory leak with some other objects but it appears to happen whenever executing this package through the .Excecute() method in my app and still remains after destroying the object. Running this much data that often is going to be a real show stopper with a memory leak.|||The issue appears to be identified and we are working on the fix for a future Service pack.|||
Hello,
We have a production server which runs a nightly batch which is quite memory intensive. The batch contains a start package which is started by SQL Server Agent, and starts other packages using the "Execute Package Task".
The server crashes completely, without logging any error information at all, at random moments during the batch, usually with an interval of a few days. The vendor of the server is investigating the hardware configuration, but we weren't able to find a problem there as of yet.
Can the issue described in this topic have anything to do with the server crashes?
Kind regards, Jeroen
