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?

From other point to the problem -- if you need to run one package from the other and both pertain to the same project -- why do you avoid using Execute Package Task? It is native to SSIS and offers you requested debug capabilities. — Ferdipux, Jul 16 '20 at 07:40
Why are you using this code in the first place? You're *creating* a new `Execute Package` task in code and use a hard-coded package path. Why not just add the proper task? If you want to change the package or parameters at runtime use expressions — Panagiotis Kanavos, Jul 16 '20 at 07:46
@Ferdipux I tried to use the native Execute Package Task within the Script, but that did not help. In the code it starts at `// test 4` — casenonsensitive, Jul 16 '20 at 08:21
@PanagiotisKanavos I know I could use expressions, the whole thing is complex and I've reduced the problem to the described situation. — casenonsensitive, Jul 16 '20 at 08:23
What is the *actual* question, *actual* problem? What are you trying to do? You can create and execute any task as if it were any other C# class, you don't need to edit the project. On the other hand, you don't need to create a package programmatically either — Panagiotis Kanavos, Jul 16 '20 at 08:42
@casenonsensitive, could you elaborate on your problem forcing you to run package from code? In my comment, I asked why you have to go to code instead of native SSIS task in the package, not in Script c# code. — Ferdipux, Jul 16 '20 at 08:42
@casenonsensitive SSIS isn't easy to program. Whatever issue you have, going into script will make it only about 1000 times harder. — Panagiotis Kanavos, Jul 16 '20 at 08:44
I created a replacement for the execute package task in a custom task. Some of its features are to start the packages via SSIS catalog and in parallel, with a configurable queue. The problem I have now is that I want to reproduce the same functionality as a ExecutePackageTask (running locally, without parallelization) in order to more easily debug some of our complex packages. When using the stock ExecutePackageTask, the debugger opens the child package allowing to follow the execution. I know CustomTask != ScriptTask, but I have to start somewhere. — casenonsensitive, Jul 16 '20 at 08:51
This may have once been possible, but I'm not sure it is anymore. There is a `DebugMode` property referenced in the appendix of [this document](https://link.springer.com/content/pdf/bbm%3A978-1-4302-0448-0%2F1.pdf). It nominally "determines whether the OnBreakpointHit event is fired". However, this property is not exposed anywhere, at least not anymore. [This is the only page](https://masudprogrammer.wordpress.com/2017/05/17/ssis-debug-the-ssis-package-dynamically-created-by-script/) I can find which references it. — allmhuran, Jul 17 '20 at 14:20
To continue along with @allmhuran's excellent find,what if in example 4 you set the @[System::DebugMode] property to true - either at design time or with a run-time override? — billinkc, Jul 18 '20 at 16:40
@billinkc I created a new SSIS project (visual studio 2019, latest updates for data tools) targeting a SQL Server 2017 project and could not find this property either in the system variables or as an available property of the object inside the script task. Perhaps it used to be exposed in earlier versions? — allmhuran, Jul 18 '20 at 16:49
@allmhuran I found a notion of **DebugMode** property usage in [MS docs](https://learn.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.dts.tasks.webservicetask.webservicetask.debugmode?view=sqlserver-2019) - seems to be an internal property for MS to speed up if not in debug. Here MS shows [an example](https://learn.microsoft.com/en-us/sql/integration-services/extending-packages-custom-objects/task/adding-support-for-debugging-in-a-custom-task?view=sql-server-ver15) implementing debugging in custom task. — Ferdipux, Jul 20 '20 at 05:04
@Ferdipux It looks like that's specific to that particular task anyway. What we seem to need here is the `DebugMode` property of the package itself. Several of the properties listed in the appendix of that interview with Donald Farmer are still visible as system variables, and available to Package objects in script tasks. But this one is not, at least not anymore. — allmhuran, Jul 20 '20 at 05:06

score 2 · Answer 1 · answered Jul 16 '20 at 07:39

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.

It's possible. Just set a breakpoint in the script. I did it last week. The entire SSIS package is *not* a separate executable. It's executed by Visual Studio itself. You can create or load a package programatically in your console application, including script tasks, and execute it. The Script tasks includes the code and compiled IL, it's not a separate process — Panagiotis Kanavos, Jul 16 '20 at 07:42
@PanagiotisKanavos, you are right regarding Script Task code debug. Topic starter wants to set a breakpoint in a SSIS package called from C# script task code and open VS SSIS debug at the child package. — Ferdipux, Jul 16 '20 at 08:05

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

1 Answers1