How to debug a script component in SSIS

Question

It's simple but I can't make it work, and I read in so many places a lot of solutions but nothing worked for me. How can I accomplished this?

Note

I put a break point in my code and ran the entire package, but it still did not work.

Synn

Answer 1

The Script component does not support the use of breakpoints. Therefore, you cannot step through your code and examine values as the package runs. You can monitor the execution of the Script component by using the following methods

Interrupt execution and display a modal message by using the MessageBox.Show() method in the System.Windows.Forms namespace. (Remove this code after you complete the debugging process.)

Raise events for informational messages, warnings, and errors. The FireInformation, FireWarning, and FireError methods display the event description in the Visual Studio Output window. However, the FireProgress method, the Console.Write method, and Console.WriteLine method do not display any information in the Output window. Messages from the FireProgress event appear on the Progress tab of SSIS Designer.

http://microsoft-ssis.blogspot.com/2011/04/breakpoint-does-not-work-within-ssis.html

Hoofed

The Script task supports the use of breakpoints during debugging, but the Script component does not have support for breakpoints check this :msdn.microsoft.com/en-us/library/ms136033.aspx – Hoofed

After changing the SSIS project to allow 32-bit debugging, I was able to debug a script task with breakpoints. The information about raising errors is good to have in the code though. – Bickel

Answer 2

Breakpoints only work in a Script Task not in a Script Component

Threefold

Not if you are on 2008! – Glabrescent

Answer 3

My issue was using the 64-bit runtime - I noticed in my output the following message "Cannot debug script tasks when running under the 64 bit version of the Integration Services runtime".

This is easily fixed by running the 32-bit runtime (e.g. temporarily) as explained here.

Tiny

Answer 4

I know the MS Blog says that breakpoints do not function in Scripts, but MSDN seems to indicate that they do. Have you tried following the directions on MSDN?

Toadstool

Debugging SSIS Script Component is only available on SSIS 2012. On 2008-- only Script Task can be debugged. – Deprivation

Answer 5

For SSIS 2008 where you can only debug the Script Task not the Script Component as already noted by Joost, I've had success using

Trace.Writeline("SSIS .....");

Then I'm capturing this output with good ol Dbgview.exe from https://learn.microsoft.com/en-us/sysinternals/downloads/debugview. I set Edit->Filter->Include SSIS* and then if you're going to leave this on your desktop set the Debug View History Depth to 99999 or it'll fill up the log with no size limit!

Glabrescent

I'm assuming the downvote is because I said you can't debug the Script Component (only the task). If you are in VS 2008 this holds true. I understand in 2012 you can now debug them. – Glabrescent

Answer 6

This answer is a cross-site duplicate of The debugger only stops at the breakpoints of the first Script Component. How do I debug more than one Script Component in an SSIS Data Flow Task? - DBA SE. It is left here for those who stumble over this old 2008 version question and need the new answer for a younger version.

---

Script components can now be debugged by placing a breakpoint in the code of the script component and executing your package.

Microsoft has updated their documentation with more details, which I am providing here as well.

To debug the code in your Script component, set at least one breakpoint in the code, and then close the VSTA IDE to run the package in SQL Server Data Tools (SSDT). When package execution enters the Script component, the VSTA IDE reopens and displays your code in read-only mode. After execution reaches your breakpoint, you can examine variable values and step through the remaining code.

This comes with two caveats:

You cannot debug a Script component when you run the Script component as part of a child package that is run from an Execute Package task. Breakpoints that you set in the Script component in the child package are disregarded in these circumstances. You can debug the child package normally by running it separately.

When you debug a package that contains multiple Script components, the debugger debugs one Script component. The system can debug another Script component if the debugger completes, as in the case of a Foreach Loop or For Loop container.

For more information, you can check out Microsoft's documentation over at https://learn.microsoft.com/en-us/sql/integration-services/extending-packages-scripting/data-flow-script-component/coding-and-debugging-the-script-component?view=sql-server-ver16&redirectedfrom=MSDN as specified in vamsi's comment.

Ibbetson

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Jeavons

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - From Review – Travis

@MichaelM. how is this a link-only answer? – Metts

@Metts OP asked how to do it and this answer essentially just says "it's possible and you can figure out how at this link". – Travis

@MichaelM. "Script components can now be debugged by placing a breakpoint in the code of the script component" how is that not explaining how to do it? – Metts

@Metts IMO that's way too vague of a statement to be considered a full answer (maybe better as a comment). Looking at the docs that were linked, there's many more details and that need to be added for the answer to be considered useful (like when those breakpoints will be ignored, etc.). – Travis

@MichaelM. the question says the asker tried this and it didn't work. this says that it now works as described in the question post. how is that not an answer? it would be better with a version number for the version it starts to work at, but how is it not an answer? If I saw it posted as a comment, I'd rather say that it should have been posted as an answer. – Metts

@MichaelM. I updated my answer - is this mor in line with the expectations of the community? Thanks! – Ibbetson

@DonovanBergin Yep! Adding those quotes from the source makes it much easier for readers and prevents link rot. +1 – Travis

I dropped the breakpoints of one script component only to stop at the breakpoints of the other. Before, the other was skipped. This answer works. – Midway

The question is for the 2008 version. That is why this answer here is out of scope. It should rather be an answer in another new question with new tags. I opened a new one at The debugger only stops at the breakpoints of the first Script Component. How do I debug more than one Script Component in an SSIS Data Flow Task? - DBA SE. Can you copy something of your answer there? – Midway

@Midway I've ported this answer over to the new one. Thanks for the assist on ensuring it lands in the right spot. – Ibbetson