2

I am able to debug custom components that are added to SSIS through Visual Studio's Attach to Process feature.

However, is it possible to debug a custom assembly while it is being executed inside of an SSIS package's script component (i.e., C# script within the data flow)? If yes, how?

I have tried the following without success:

  1. Build the assembly with breakpoints (see Note at the bottom of this question)*
  2. Start the SSIS package and halt the execution at a breakpoint
  3. Attached the instance of Visual Studio that has the assembly file open to the executing instance of DtsDebugHost.exe that is running the SSIS package

All of the breakpoints within the assembly file switch over to say:

The breakpoint will not currently be hit. No symbols have been loaded for this document.

What symbols am I supposed to load? I checked the modules window, but loading the three assemblies do not help and when I try to load symbols for the two SqlServer DTS assemblies it requires me to pick a very specific file of the same file name and file type where both appear to be temporary file names - I have no idea what to load here.

Modules Window

*Note: I have the following post-build events setup on the custom assembly:

"C:\Program Files (x86)\Microsoft SDKs\Windows\v10.0A\bin\NETFX 4.8 Tools\gacutil.exe" -u $(TargetName)
"C:\Program Files (x86)\Microsoft SDKs\Windows\v10.0A\bin\NETFX 4.8 Tools\gacutil.exe" -i $(TargetFileName)
copy $(TargetFileName) "C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.8"
copy $(TargetFileName) "C:\Program Files (x86)\Microsoft Visual Studio\2019\Professional\Common7\IDE\CommonExtensions\Microsoft\SSIS\150\Binn"

UPDATE 1

Before posting this question, I also tried adding the assembly's source code files into the script component's, but that resulted in other errors being thrown that were unrelated to the original error that I was trying to debug. There is a lot of recursion inside the assembly and it also has code that tries to load it's own assembly. I was unable to understand exactly what was causing the unrelated errors. So, I gave up on that route.

One other point that is perhaps worth mentioning, if not strictly for programmer humor, this assembly is very old with little to no code comments. When I found it's source code in our code repo it would not open because it was still targeting .NET Framework 2.0, which is not even available for download from Microsoft. I had to upgrade it, so I selected .NET Framework 4.8. I tried switching to 4.7 as the script component targets 4.7 by default and is somewhat hard-coded that way because every time I save and re-open it, it downgrades back to 4.7. But, neither .NET Framework versions resolve my ability to debug the assembly within the script component.

So, I have resorted to adding text file writelines that output where the code reaches before erroring out. This is a lame way to debug IMHO.

UPDATE 2

I attempted to load the symbols for Microsoft.SqlServer.ManagedDTS.dll and the open-file dialog window is looking for either Microsoft.SqlServer.ManagedDTS.pdb or Microsoft.SqlServer.ManagedDTS.dbg. I went into C:\Program Files (x86)\ and ran the following:

  • dir /s /b Microsoft.SqlServer.ManagedDTS.pdb
  • dir /s /b Microsoft.SqlServer.ManagedDTS.dbg

But, the response to both was File Not Found. Any ideas?

Load ManagedDTS symbols

J Weezy
  • 3,507
  • 3
  • 32
  • 88
  • 2
    A Script Task is a .NET project with an output type of Class Library. When I was in your place (SQL 2005/2008 era), I faked it. I had a console app that I used to simulate the environment. The other approach that comes to mind is to add your project into the existing Script Task. That way, when the SSIS Task builds and you exit, all the source code - yours and the Tasks will be serialized into the SSIS package. On the assembly question, I think it's ManagedDTS you want as you're in Task space. Component needs runtime and a tx? named assembly – billinkc Nov 20 '21 at 15:33
  • @billinkc I tried adding the assemblies source code files into the script components, but that resulted in other errors being thrown that were unrelated to the original error that I was trying to debug. There is a lot of recursion inside the assembly and it also has code that tries to load it's own assembly. So, I gave up on that route. Regarding your last point, can you please provide an answer on how to load symbols for ManagedDTS? It opens a window looking for a specific, random looking, file name. I haven't done this before. Thanks. – J Weezy Nov 20 '21 at 16:49
  • 1
    Depends on what you have installed but try `C:\Program Files (x86)>dir /s /b Microsoft.SqlServer.ManagedDTS.dll` C:\Program Files (x86)\Microsoft SQL Server\150\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\PublicAssemblies\SSIS\140\Microsoft.SqlServer.ManagedDTS.dll – billinkc Nov 20 '21 at 17:18
  • @billinkc I have attempted what you recommended and posted an update (i.e., Update 2) to my question. Any assistance or recommendations that you can provide is greatly appreciated. – J Weezy Nov 22 '21 at 16:25
  • I don't know how I have a copy of the DLL under Visual Studio. I presume the existence under SQL Server is related to my installation of the developer edition of SQL Server and as part of that, I selected "Install SQL Server Integration Services Service" but these are just guesses – billinkc Nov 22 '21 at 17:12

1 Answers1

1

I was not able to find a way to debug the assembly during the execution of the SSIS package. I had to retrieve the row's data from the SSIS package and then feed it into the assembly through a console application, as @billinkc had previously suggested. I will leave this question up. If anyone has a better answer in the future then please do share it.

J Weezy
  • 3,507
  • 3
  • 32
  • 88