Helpful information and examples on how to use SQL Server Integration Services.
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.
As with previous versions of SSIS, you can also monitor the execution of the Script component by using these methods:
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.