Debugging SSIS Script task - Breakpoint is enabled but it does not hit

Question

I am working on an SSIS package. The package has a script (C# language) task. I need to debug the script task. I set the break point. The script in script editor (Visual Studio) and the task in SSIS package editor, both, show break point in red color - means the break point is enabled. However, when I debug the package the break point does not hit.

The break point has no conditions on it, so I expect it to hit every time the package runs.

I am using Visual Studio 2008 on Windows 2003 R2 64-bit SP2.

What happened why you tried to run the package, the script task executed successfully or not? — LONG, Jan 04 '17 at 19:18
@LONG yes the script executes successfully when I run the package. — HappyTown, Jan 04 '17 at 23:55
@Long no `try..catch`. I simplified the script eliminating all other logic to just append few hard coded strings to a `StringBuilder` instance. — HappyTown, Jan 05 '17 at 19:03
This was my issue, and it must be a bug! https://developercommunity.visualstudio.com/content/problem/349680/gettersetter-variables-make-breakpoints-stop-worki.html — Oliver, Jul 23 '19 at 19:28

score 58 · Accepted Answer · edited May 23 '17 at 18:32

58

After more research and trial-error, found that the SSIS package ignores the breakpoints in a Script Task when debugging on a 64-bit machine. To fix it -

Go to the Solution Explorer
Right click your SSIS project node > Properties
In Configuration Properties > Debugging > Debug Options > Set Run64BitRunTime to False.

SSIS Project configuration settings

After making this change, the breakpoints hit like magic.

edited May 23 '17 at 18:32

Mathieu Guindon

69,817
8
107
235

answered Jan 05 '17 at 19:13

HappyTown

6,036
8
38
51

1

There are a whole lot of different permutations of all the suggestions in this thread, but I'm able to debug fine using the 64-bit runtime (but I am building the script task as x86, per below). – Brian May 21 '18 at 21:26
uh no they dont – Squibly Mar 16 '22 at 03:19

PTD · Answer 2 · 2020-02-06T10:45:18.890

37

I tried all the answers provided here with no success (using VS2015). After some more searching I found this question that is actually an answer which stated that newer C# features / syntax were causing the debugger to not fire correctly.

In their example (and also mine) using string interpolation was causing the breakpoints to not be hit.

Replacing

$"{someVariable} - {someOtherVariable}"

with

string.Format("{0} - {1}", someVariable, someOtherVariable);

did the trick for me.

EDIT

It appears this issue has now been fixed with SQL Server Integration Services Projects, but you need to be running VS2019 to download it.

edited Feb 06 '20 at 10:45

answered Mar 12 '18 at 19:54

PTD

1,028
1
16
23

1

It didn't like string.join either in VS 2015. – dotnetesse Mar 13 '19 at 19:30
1

This works for me. Thank you for sharing your finding. :) – Om. Mar 28 '20 at 06:36
It seems that all the efforts here are taking stabs and sometimes working and sometimes not. Thank MS as @PTD said the SQL Server Integration Services Projects update fixes this problem. In order to install it though I needed to update my VS 19 first using the VSIX (Visual Studio Installer). His response should be the chosen answer... – Noah Joseph Anderson Jun 26 '20 at 20:18
You're a genius! You just saved me useless hours of pulling my hair out. Thank you, thank you! – Vippy Feb 18 '22 at 19:24

it3xl · Answer 3 · 2018-08-13T18:08:38.823

Update: Guys, I againg lost any ability to set breakpoints (a request to MS)
My previous fixes are below.
Now I'm using logging and tracing instead of debugging.

C# new features (after C# 4.0) are blamed for killing debugging of the SSIS Script Task.

To return the breakpoint capability I do the following.

Remove C# new features
Run my Script Task once, successfully. I.e. without a crash.
Reopen the Vsta Project from my Script Task and put breakpoints there.

At the end, you have to see a red circle on your Script Task.
(Tested in VS 2017.)

Note. I have to mention that debugging works even if your use "Execute Task" only, not "Execute Package"!

Remove C# new features

To remove the C# new features, I can advise you two ways.

First, restrict Vsta Project properties to C# 4.0 (migrated packages may not support this).

Dobule click your "Script Task" to open "Script Task Editor".
Click "Edit Script..." button to open Visual Studio.
In "Solution Explorer" select the project and click the F4 key on your keyboard.
In opened "Properties" window in "C# Language Level" choise "C# 4.0"
Build your project and fix compilation errors.

Secondly, Vsta Projects in old/migrated packages may not show the above "C# Language Level" property.
So you can put your code in a fake project in Visual Studio 2010 and compile it there.

Run it once successfully

After you fix your C#, you have to run your Script Task once successfully.
You may want to put the return statement at the beginning of the Main() method to prevent any real execution.
Sorry, this doesn't always work and I don't realise why but you definitely need to fix your C# in the first place.
At least you will get a working Script Task and can debug it in an old fashioned way (logs by Dts.Events..., exceptions, etc.)

TL;DR

It looks like I even got severe cases when C# new features forced Script Tasks to fail silently with a success completion status.

As an example, add the following to your Script task.

string Bug { get; } // Only getter properties.
//...
var str = $"Now is {DateTime.Now}"; // String Interpolation in C#
//...
var dummy = val?.ToUpper(); // ?. and ?[] null-conditional Operators

And workarounds for this non-complete list:

string Bug { get; set; }
//...
var str = string.Format("Now is {0}", DateTime.Now);
// etc.

What I also do, I build my C# code in Visual Studio 2010. It simply doesn't compile the new .NET features and do not allows .NET Framework versions above 4.0. So far, so good.

Of course, other answers from this SO-question didn't help me.

Note that this hase been fixed in Version 3.2 Preview (2019-11-05) https://marketplace.visualstudio.com/items?itemName=SSIS.SqlServerIntegrationServicesProjects — Janis Veinbergs, Dec 04 '19 at 12:16

score 9 · Answer 4 · answered Jul 24 '18 at 11:41

9

In my case, none of these solutions worked. I finally got to know that the Resharper was culprit. After uninstalling it, it started working like charm.

answered Jul 24 '18 at 11:41

Sumanth

91
1
1

1

Indeed, seems to be a registered issue on the [Jetbrains support forum](https://youtrack.jetbrains.com/issue/RSRP-468470). Disabling resharper fixed it for me too. Didn't try unchecking debug options yet within resharper. – Doc Jul 27 '18 at 12:08
2

Disabling only the debugger integration of Resharper also works. ReSharper -> Options -> Products & Features and then unticking the "Debugger integration" box. – Andris Apr 16 '19 at 11:14
Yup...why does it take multiple times to tell me something before I remember it?! This was my issue as well. – Catchops Feb 26 '20 at 17:45

score 7 · Answer 5 · answered Mar 27 '18 at 07:40

7

In my case, I had to get rid of all features from C# 6: string interpolation, null conditional operators (?., ?(), ?[]) and expression-bodied members (=>) (there might be more in your case). You can check them all here. Of course, the same applies to C# 7 features.

The 32/64 bit changes from other answers didn't help, so I rolled back those and the debugging kept working just fine.

answered Mar 27 '18 at 07:40

Andrew

7,602
2
34
42

And for me, I had to do both: change the target platform to x86 and remove the string interpolation. – Brian May 21 '18 at 21:28
In my case, __verbatim string literal__ and __interpolated strings__ was the culprit `string query = $@" very long string with new lines...` – Aaron C Apr 05 '21 at 19:18

Shadmehr · Answer 6 · 2017-01-05T19:35:07.700

5

Use System.Diagnostics.Debugger class to add breakpoint programmatically:

System.Diagnostics.Debugger.Launch();
System.Diagnostics.Debugger.Break();

You can check if the debugger is attached or not:

    if (System.Diagnostics.Debugger.IsAttached)
        System.Diagnostics.Debugger.Break();

Follow these step:

Keep your project or solution opened.
Run your app to hit breakpoint.
Select your project in Just-In-Time Debugger.

edited Jan 05 '17 at 19:35

answered Jan 04 '17 at 19:16

Shadmehr

278
4
13

Thanks. I tried these. I would get a message box saying user breakpoint reached but would not let me step through the code. – HappyTown Jan 05 '17 at 19:17
Open your project before run your app.Then select your project in Just-in Time debugger. – Shadmehr Jan 05 '17 at 19:28

score 5 · Answer 7 · answered Feb 22 '17 at 03:22

I inherited an SSIS package where unfortunately the above answers didn't help.

In the end I found the script task's build properties for debug mode had had the optimize code ticked. Make sure this isn't ticked because for me visual studio would fire up for script debugging and close shortly after without breaking at all.

Pretty obscure but worth a mention.

Ben Watt · Answer 8 · 2018-07-27T10:20:38.417

5

We hit the same problem recently. For us the solution was to ensure that the script task project was marked to run as with the platform target set to x86.

Edit the script task
Click on the project and select properties
Select to set the platform target to x86

edited Jul 27 '18 at 10:20

answered Aug 17 '17 at 10:49

Ben Watt

51
2
3

This fixed it for me, and also resolved the following warning I was getting in the Error List: "MSB3270: There was a mismatch between the processor architecture of the project being built..." https://learn.microsoft.com/en-us/visualstudio/msbuild/errors/msb3270 – spinjector Apr 23 '22 at 16:31

score 2 · Answer 9 · answered Aug 08 '17 at 16:00

2

In addition to Jeff's suggestion, also change the Platform Target to "x86" (In the script's properties' Build tab. This FINALLY got me debugging again on a 64-bit system.

answered Aug 08 '17 at 16:00

Kurt S

21
3

score 1 · Answer 10 · answered Aug 18 '20 at 09:50

Microsoft released an update v3.2 of SQL Server Integration Services Projects where it resolves the issue with Roslyn and other C# language features introduced after .Net 4.5. C# features.
Bad news - this fix is for Visual Studio 2019 only, you have to upgrade your VS to use it.

score 1 · Answer 11 · answered Jul 16 '21 at 01:53

I spent whole day on this and NONE of the solutions mentioned here worked for me.

In my case, the existing project was targeted to SQL Server 2016 which defaults ScriptLanguage Microsoft Visual c# 2015. This doesn't allow debugging in VS 2019. I have to target project to SQL Server 2019 to make debugging work. Of course, I am not going to checkin version change. It's only to debug script. Once script is working, I am going to revert target version to SQL server 2016.

Hope this saves time for someone.

score 1 · Answer 12 · edited Sep 08 '21 at 20:06

1

I had the same problem as you @PAS. Using VS 2019 and Target server version 2016. Just found out that if upgrading SSIS in Visual Studio (going into Extensions->Manage Extensions) to latest version (which now is 3.15) debugging is now working.

edited Sep 08 '21 at 20:06

bad_coder

11,289
20
44
72

answered Sep 08 '21 at 13:57

Steve

11
1

score 0 · Answer 13 · answered Apr 03 '18 at 08:56

My breakpoints refused to hit no matter what I did. I ended up debugging and fixing the issues just using exception throws. Once I had fixed the issues I was having, the breakpoints started hitting!

So my breakpoints would only hit once the code did not experience any runtime issues... which is bizarre.

score 0 · Answer 14 · answered May 07 '18 at 12:09

In my experience, it doesn't matter:

if Run64BitRuntime is true or false
if you build the 32 or 64 bits version of your package

But there is something very important, not mentioned in any other answer: you must run the whole package. If you run a Task or a Container the breakpoint will be ignored.

I'm using Visual Studio 2013, on a 64 bits machine.

score 0 · Answer 15 · answered Sep 10 '18 at 11:23

0

I only had one Script component were no breakpoints were hit (I was doing some CRM stuff without needing source/target). I trid to add a Source componenet with a simple fetchXML (even if I didn't needed it). Then it worked! :-)

answered Sep 10 '18 at 11:23

Marius Holm Enerud

21
1

score 0 · Answer 16 · answered Apr 20 '20 at 22:00

I found out that by copying a Script Component task, the VSTA project as a whole is copied as well. This is what you would expect, but what I did not expect is with that, the assembly name for example is also copied. Running then Execute Task works fine, but running the whole package actually only runs the first script that was copied and resulted in exceptions being thrown before ever hitting the row processing function. That was also the reason for me that breakpoints were not being hit.

Debugging SSIS Script task - Breakpoint is enabled but it does not hit

16 Answers16

Linked

Related