Archive for January 2008

New Tool to Help With Partitioned Tables

Stuart Ozer on the SQL Server Customer Advisory Team (CAT) has posted a new tool to CodePlex to help with the management of "staging" tables for swapping data in and out of partitioned tables. Since this is a common scenario in data warehousing scenarios, I’m really looking forward to trying this out on my next project. If you are interested in it, you can see a description here, and download the code here.

Handling Multiple Errors in SSIS Revisited

Previously, I posted about how multiple errors in SSIS can be combined into a single unit for emailing or logging. Recently, a thread on the MSDN forums pointed out that the example wasn’t quite complete. While it does show how to collect all the error messages and get them into a single string, it doesn’t show how to email or log that string only once. So I’m giving it another shot, and show two options for handling this. This post does build on the previous one, so please read it before proceeding.


If you download the sample package in the previous post and examine it, you’ll see that there is a script task in the OnPostExecute event. This script task is where the error messages are assembled, and the code to deliver the bundled error message would be placed. Please note – the script does not actually email or log the message, that was left open for the reader to implement.


If you run the package, you’ll notice that the OnPostExecute event fires twice. It’s firing once for the data flow, and once for the package. Since the OnPostExecute event handler is where the script is located, this causes the message to be delivered twice – not what we want. To work around this, we need to make sure the OnPostExecute only fires once. One way to handle this is to set the Propagate system variable to false. The Propagate variable controls whether the event is raised to the next container. In this case, if Propagate is set to false on the data flow’s OnPostExecute event, the package’s OnPostExecute will only be fired once.


image


However, this approach requires that all tasks in the package need to have the Propagate variable set to false in their OnPostExecute event handlers. To work around this, I recommend incorporating a single Sequence Container, with the Propagate variable set to false on it’s OnPostExecute. Any tasks in the package should be added inside the Sequence Container. If you do this, the Propagate variable only needs to be set once.


image


Another approach is to eliminate the use of the OnPostExecute event altogether. To do this, the Script Task needs to be moved to the control flow, and the OnPostExecute event handler can be deleted altogether. A Sequence Container should still be used to hold all of the tasks that need to be grouped for error handling. The Script Task should be connected to the Sequence Container with a Failure constraint.


image


This approach also ensures that the Script Task will only execute once, if there is a failure in the Sequence Container.


Hopefully, this helps clarify how to leverage collecting the error messages to only send a single error result. I’ve uploaded the samples here, so please feel free to take a look at them and let me know if you have questions.

Resetting the View in the SSIS Designer

There’s a minor annoyance in SSIS with the way the diagram of the package is saved. If you’ve ever spent time working in a package with a lot of objects, you’ve probably encountered it. You move some objects around, save the package, close it, then reopen it. When the package is displayed, the objects are off center, or maybe don’t even show up. This is because the view is scrolled so that all the objects aren’t in the initial viewable area.


If you want a little background on what’s happening, read this paragraph. Otherwise, skip ahead to the next one. Each object in the diagram is saved with it’s x (Horizontal) and y (Vertical) coordinates persisted in the DTSX file. In addition, the view’s current Left and Top position is saved as well. These settings tell the IDE how to lay out the diagram, and how to initially position the view of it.


To reset the initial view, you need to alter the XML for the package directly. As with any direct editing of the XML, it’s not supported, and you should definitely make a backup of the package first. You need to alter the PersistedViewPortLeft and PersistedViewPortTop properties. Be aware, there may be multiple copies of these values, one set for the control flow, and one set for each data flow defined in the package. You can set these values to 0 in order to reset the view to a 0,0 top left corner.


The XML tags you are looking for look like:


<dwd:PersistedViewPortLeft>0</dwd:PersistedViewPortLeft>


They may also appear as escaped versions of the XML (not sure why, some of my packages do this, others display normally):


&lt;dwd:PersistedViewPortLeft&gt;0&lt;/dwd:PersistedViewPortLeft&gt;


Just change the value between the tags to set the property value.