Script Component Debugging in SSIS 2012

In SQL Server 2012, you can now debug the Script component by setting breakpoints and running the package in SQL Server Data Tools (replaces BIDS) .

When the 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.

On a side note, we upgraded the scripting engine to VSTA 3.0, which provides a Visual Studio 2010 shell and support for .NET 4.

Here are a few things to keep in mind when debugging the Script component.

You can't 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.

As with previous versions of SSIS, you can also monitor the execution of the Script component by using these methods:

Interrupt execution and display a modal message by using the MessageBox.Show method in the System.Windows.Forms namespace.

For Scripts – you'd have to try it out. I think the main limitation with VSTA is that you have a single C#/VB.NET project. It looks like certain VS plugins (like Resharper) that I use for my regular C# development also work in the VSTA IDE, so I can take advantage of them when writing scripts.

If I had a lot of script logic, I'd consider putting in a shared DLL so I could unit test it outside of SSIS (as well as turning it into a custom task/transform).

I actually was planning on experimenting with that, not sure what test framework to go with, the choice is overwhelming whereas it seems the majority of developers being very keen to using NUnit. What would be your word of advise?

For NUnit I can create the library project in VS then reference the DLL in the Script Task then hopefully test.

Interesting, I have the Resharper but is does get fired up in my case (in BIDS 2008). Would it in VS 2008?

You know, I use Snippet Compiler for Script Task coding and/or prototyping and then, yes it often times becomes a DLL, but that I if re-usability is involved. But some Scripts I develop are like real applications by themselves. In some places VS is not available, only BIDS, this is a reality.

There are Visual Studio Test Edition-based unit tests for perfoming automated unit testing on data flow components. The "Delimited File Reader Source Sample" on CodePlex (sqlsrvintegrationsrv.codeplex.com/…/17646 ) shows how to use these tests, according to the sample description.

However, based on the MSDN documentation, I think you'd need to create a Unit Test Project to test the methods in your script code. (msdn.microsoft.com/…/hh598957.aspx ).

Script task debugger does not work for me (clean OS install, SQL2012 SSDT + SP1). I created a simple task with 2 lines of custom code. It was working for the first run, but after the second stopped working.

We have a team that recently started using Visual Studio 2012, converting SSIS packages from 2008, and seem to be unable to set breakpoints in Script Tasks. The error we're receiving says "Cannot start debugging. Pre-debugging negotiations with Host failed." Cannot seem to find any information about this error, or what's causing it. There is also a brief flash of a dialog box that says "Visual Studio has encountered an unexpected error" but that disappears quickly.

@Igschmidt, I found this page trying to find a solution for this problem. I was able to resolve it by ensuing that the script parameters were correct. I had changed the name of one of the parameters, but I forgot to change the name in the list of parameters that are passed to the script.

I am using SQL Server 2012.I am facing a problem when trying to debug the script. When I execute the package (F5) the break point that i placed in the script never breaks. What happens is VSTA opens and it stays blank (the file where break point is present does not open) with a status message at the bottom saying build succeeded and nothing happens. It looks like VSTA is not entering debug mode (i could say this because i am able to build the code in VSTA which is opened when i run the package). My problem is very similar to the one mentioned in link below