Script Component Debugging in SSIS 2012

Script Component Debugging in SSIS 2012

Rate This
  • Comments 17

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.
  • Raise events for informational messages, warnings, and errors. For more information, see the Developer's Guide topic, Raising Events in the Script Component.
  • Log events or user-defined messages to enabled logging providers. For more information, see the Books Online topic, Logging in the Script Component.

Leave a Comment
  • Please add 2 and 4 and type the answer here:
  • Post
  • This is a very welcome feature. Thank you!

    Question: does this debugging enhancement also include the "edit and continue" feature?

  • Greatly awaited time-saving feature..!

  • Because the VSTA IDEdisplays your code in read-only mode, you can't edit the code.  You can step through the code and you can click Continue.

  • So the next release of SSIS project development is not Visual Studio 2010 based?

  • Hi Arthur - sorry, that should read "VSTA 3.0 ... which provides a Visual Studio 2010 shell". I'll fix that in the text.

    SQL Server 2012 ships will support for Visual Studio 2010 SP1 ... we're working on a plan for supporting the next version of VS as well.

  • Thank you.

    Also forgot to ask: will we get the possibility of integration with code testing e.g. Plex or the built-it VS testing facilities (it is e.g. when we can execute tests right from the task component)?

    As far as I know , currently, this is not possible even with say NUnit, correct?

  • Nothing like that for SSIS packages in SQL 2012.

    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.

  • Finally! :)

  • Yeah is there a way to unit test the Script component methods?

  • Hello Dinesh,

    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 ).

    The "Creating Automated Tests" MSDN topic (msdn.microsoft.com/.../dd380755.aspx ) provides more information about Visual Studio automated tests.  

  • 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.

  • Script component binary code not foudn error.

    I compiled the code and the copilation is successfull. But I am alwyas gettinging binary code not found error,

  • This is what I was looking from 2005 :)

    I have been using this feature from last two and a half year and didn't find any single issue

  • 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.

    Any suggestions, ideas, etc.?!

    Thanks,

    Larry

Page 1 of 2 (17 items) 12