How to execute a package task in a script task in debug mode in VS

Question

I have an SSIS project containing two packages. Package1.dtsx should be called by Package2.dtsx. When doing so with an ExecutePackageTask, the debugger opens the second package once it gets called.
Now I need to adapt the code so that I'm not using the ExecutePackageTask but rather a ScriptTask. But then, the debugger won't open the second package.
Package2.dtsx now looks like this:

Here are the different things I tried within the ScriptTask:
    public void Main()
    {
        var parentPackage = (Package)Dts.Variables["System::StartTime"].Parent;
        var proj100 = (IDTSProject100)parentPackage.Project;
        string packageStreamName = "Package1.dtsx";

// test 1 - using IDTSProject100.GetConfiguredPackageByName
        var pkgTest1 = proj100.GetConfiguredPackageByName(packageStreamName);
        pkgTest1.InteractiveMode = true;
        var res1 = pkgTest1.Execute();

// test 2 - using IDTSProject100.GetPackageByName
        var pkgTest2 = proj100.GetPackageByName(packageStreamName);
        pkgTest2.InteractiveMode = true;
        var res2 = pkgTest2.Execute(pConnections: proj100.GetConnections(), pVariables: proj100.GetVariables(), pEvents: null, pLog: null, pTransaction: Dts.Transaction);

// test 3 - using reflection to get the Project object and then load the package from the PackageItems
        System.Reflection.PropertyInfo pInfo = proj100.GetType().GetProperty(
                        "Project",
                        System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance);
        Project proj = (Project)pInfo.GetValue(proj100, null);

PackageItems pis = proj.PackageItems;
        PackageItem childPackageItem = pis[packageStreamName];
        Package pkgTest3 = childPackageItem.LoadPackage(null);
        pkgTest3.InteractiveMode = true;
        var res3 = pkgTest3.Execute();
        
        // test 4 - using a new package added to the current project. This package contains an ExecutePackageTask that calls the child
        Package pkgTest4 = new Package();
        Executable execPackageTask = pkgTest4.Executables.Add("STOCK:ExecutePackageTask");
        var taskHost = (TaskHost)execPackageTask;
        var execPkgTask = taskHost.InnerObject as ExecutePackageTask;
        execPkgTask.UseProjectReference = true;
        execPkgTask.PackageName = packageStreamName;
        //var execRes = taskHost.Execute(connections: Dts.Connections, variables: Dts.Variables, events: null, log: null, transaction: Dts.Transaction);

pkgTest4.InteractiveMode = true;
        proj.PackageItems.Add(pkgTest4, "test.dtsx");
        var res4 = pkgTest4.Execute();
    }

Any ideas what I could be missing? Is it even possible?

Ferdipux · Answer

I do not think it is possible to invoke a Visual Studio SSIS debugger from C# code.
Preface to this statement. SSIS Script task is a .NET independent language executable placed in a special placeholder. You can run VS debugger on the .NET code itself. From the structure point of view, when VS runs or debugs the SSIS package, it runs it in DtsDebugHost.exe process. VS executes SSIS package task by task knowing its structure, capturing events and progress and displaying it in its window.
The Script Task executes its code, your code samples are fine examples on how to get running Project and execute a Package from it. But - from VS point of view - it is an invocation of some class methods. You can proceed with Package output, capturing all task and component messages and then - store it in file or DB text field.

How to execute a package task in a script task in debug mode in VS

One Answer

Add your own answers!

Ask a Question