February 2010

You are currently browsing the monthly archive for February 2010.

A while ago I posted a query to create a list of all the Integration Services packages deployed to the MSDB.  I am now using that query to take it a step further.

If you’ve been using SSIS for a while you’ve probably noticed that the Management Studio doesn’t like to delete Integration Services folders that are not empty.  In fact, it will politely ask you if you’re sure that you want to delete the folder on which you’ve just selected the “Delete” option through the right-click menu.

Right-click pop-up menu on SSIS folder

I am sure I want to delete this non-empty SSIS folder

So you click the Yes button.  But then it shows you the following message:

SSIS folder ‘FolderWithSubfolders’ contains packages and/or other folders. You must drop these first. (Microsoft SQL Server Native Client 10.0)

Graphically it looks like this:

Object Explorer pop-up: you can't delete SSIS folders that contain packages or other folders

And this message can be really annoying if you’ve got a main folder with, let’s say, five subfolders, and each subfolder contains about 20-30 packages.  If you want to delete this folder you first need to delete each package separately and then delete the five subfolders, and then you can finally delete the main folder.  And all that through the right-click pop-up menu because you can’t just select the object in the Object Explorer and hit the Delete button on the keyboard – it doesn’t have an action on SSIS objects…

So, I wasn’t planning on doing such a job manually and came up with the following stored procedure.

It’s probably a bit long but don’t run away just yet, I will explain what’s going on down below the code, and there are some comments in the code as well.

/*
DESCRIPTION: Deletes all folders and packages under, and including, specified folder.
WRITTEN BY:  Valentino Vranken
CREATED:     2010-02-28
VERSION:     1.0
USAGE:
  -- mind the forward slash
  EXEC dbo.SSIS_RecursiveDeleteFolder '/FolderWithSubfolders'
  -- to delete a subfolder
  EXEC dbo.SSIS_RecursiveDeleteFolder '/FolderWithSubfolders/ASubfolderWithPackages'

COPIED FROM: http://blog.hoegaerden.be

Note 1: folder names are not case-sensitive
Note 2: uses system tables and (undocumented) stored procedures located in MSDB.
Note 3: this code was written for SQL Server 2008. For 2005:
  o sysssispackagefolders -> sysdtspackagefolders90
  o sysssispackages -> sysdtspackages90
  o sp_ssis_deletefolder -> sp_dts_deletefolder
  o sp_ssis_deletepackage -> sp_dts_deletepackage
*/
CREATE PROCEDURE dbo.SSIS_RecursiveDeleteFolder
    @Folder varchar(2000)
AS
BEGIN
    set nocount on;

    declare @foldersToDelete table
    (
        folderid uniqueidentifier,
        Lvl int
    );

    declare @packagesToDelete table
    (
        PackageName sysname,
        folderid uniqueidentifier,
        Lvl int
    );

    --retrieve list of folders to be deleted
    with ChildFolders
    as
    (
        select PARENT.parentfolderid, PARENT.folderid, PARENT.foldername,
            cast('' as sysname) as RootFolder,
            cast(PARENT.foldername as varchar(max)) as FullPath,
            0 as Lvl
        from msdb.dbo.sysssispackagefolders PARENT
        where PARENT.parentfolderid is null
        UNION ALL
        select CHILD.parentfolderid, CHILD.folderid, CHILD.foldername,
            case ChildFolders.Lvl
                when 0 then CHILD.foldername
                else ChildFolders.RootFolder
            end as RootFolder,
            cast(ChildFolders.FullPath + '/' + CHILD.foldername as varchar(max))
                as FullPath,
            ChildFolders.Lvl + 1 as Lvl
        from msdb.dbo.sysssispackagefolders CHILD
            inner join ChildFolders on ChildFolders.folderid = CHILD.parentfolderid
    )
    insert into @foldersToDelete
    select F.folderid, F.Lvl
    from ChildFolders F
    where F.FullPath like @Folder + '%';

    --retrieve list of packages to be deleted
    with ChildFolders
    as
    (
        select PARENT.parentfolderid, PARENT.folderid, PARENT.foldername,
            cast('' as sysname) as RootFolder,
            cast(PARENT.foldername as varchar(max)) as FullPath,
            0 as Lvl
        from msdb.dbo.sysssispackagefolders PARENT
        where PARENT.parentfolderid is null
        UNION ALL
        select CHILD.parentfolderid, CHILD.folderid, CHILD.foldername,
            case ChildFolders.Lvl
                when 0 then CHILD.foldername
                else ChildFolders.RootFolder
            end as RootFolder,
            cast(ChildFolders.FullPath + '/' + CHILD.foldername as varchar(max))
                as FullPath,
            ChildFolders.Lvl + 1 as Lvl
        from msdb.dbo.sysssispackagefolders CHILD
            inner join ChildFolders on ChildFolders.folderid = CHILD.parentfolderid
    )
    insert into @packagesToDelete
    select P.name, F.folderid, F.Lvl
    from ChildFolders F
        inner join msdb.dbo.sysssispackages P on P.folderid = F.folderid
    where F.FullPath like @Folder + '%';

    --use cursor to loop over objects to be deleted
    declare objectsToDelete_cursor cursor
    for
        select P.folderid, P.Lvl, P.PackageName, 'P' as ObjectType
        from @packagesToDelete P
        UNION ALL
        select F.folderid, F.Lvl, null, 'F'
        from @foldersToDelete F
        order by Lvl desc, ObjectType desc;

    open objectsToDelete_cursor;

    declare @folderid uniqueidentifier;
    declare @lvl int;
    declare @packageName sysname;
    declare @objectType char;

    fetch next from objectsToDelete_cursor
    into @folderid, @lvl, @packageName, @objectType;

    while @@FETCH_STATUS = 0
    begin
        if @objectType = 'F'
        begin
            print 'exec msdb.dbo.sp_ssis_deletefolder '
                + cast(@folderid as varchar(max));
            exec msdb.dbo.sp_ssis_deletefolder @folderid;
        end
        else
        begin
            print 'exec msdb.dbo.sp_ssis_deletepackage '
                + @packageName + ', ' + cast(@folderid as varchar(max));
            exec msdb.dbo.sp_ssis_deletepackage @packageName, @folderid;
        end

        fetch next from objectsToDelete_cursor
        into @folderid, @lvl, @packageName, @objectType;
    end;

    close objectsToDelete_cursor;
    deallocate objectsToDelete_cursor;
END

Before trying to dismantle this stored procedure, I recommend you to read my previous article on retrieving the list of packages.  That already explains half of the code, if not 75%.

Our mission is to find a way to recursively delete packages and folders contained in a specified folder.  To be able to loop over those objects in the correct order (from the deepest level up until the level of the folder specified), the SP creates two table variables: one to hold all folders under the specified folder (@foldersToDelete) and one to hold the packages under the specified folder, including all subfolders (@packagesToDelete).

Based on those two lists I create a cursor that joins these two together, taking their level and object type into consideration.  That’s important because we first need to delete the packages in the lowest level folder, followed by their containing folder, then move one level up and do the same.

We then use the cursor to loop over the packages and folders and use two undocumented system stored procedures – one for each object type- to delete the package or folder.  These system SPs are located in the MSDB.  Here’s how they are defined:

ALTER PROCEDURE [dbo].[sp_ssis_deletefolder]
  @folderid uniqueidentifier
AS

ALTER PROCEDURE [dbo].[sp_ssis_deletepackage]
  @name sysname,
  @folderid uniqueidentifier
AS

As you can see, the parameters for these procedures are not that complicated.  Both of them expect a uniqueidentifier as identification for the folder.  That’s okay, these IDs are stored in the msdb.dbo.sysssispackagefolders table and retrieved by our queries to create the list of to-be-deleted objects.

Furthermore, the sp_ssis_deletepackage SP expects the name of the package to be deleted.  Not a problem either, those names are obtained from the msdb.dbo.sysssispackages table.

Note for SQL Server 2005 users: this code was written for SQL Server 2008.  The system stored procedures and system tables exist in 2005 as well, but they have different names.  See the comment header of my SP for more details.

So, let’s give it a little test.  Following screenshot shows the setup.  What I will do is use the stored procedure to delete the FolderWithSubfolders folder.  If you’ve been paying close attention, that is the same folder which I tried to delete manually through the Management Studio’s right-click menu (see first screenshot above).

Overview of my deployed folders and packages

After creating the SP, I ran following command:

EXEC dbo.SSIS_RecursiveDeleteFolder '/FolderWithSubfolders'

And that gave me the following output in the Messages pane:

exec msdb.dbo.sp_ssis_deletepackage AnotherPackage, 7F38288D-4370-40A8-80E3-E92283033E4C

exec msdb.dbo.sp_ssis_deletepackage Package, 7F38288D-4370-40A8-80E3-E92283033E4C

exec msdb.dbo.sp_ssis_deletefolder 4102ED59-ED75-4D93-BBAE-0A162447BF02

exec msdb.dbo.sp_ssis_deletefolder 7F38288D-4370-40A8-80E3-E92283033E4C

exec msdb.dbo.sp_ssis_deletefolder C156B436-8C78-4BF9-99F9-5ABFAB10C405

I have deliberately put a couple of print commands in the stored procedure to dump the commands that are actually being executed.  This gives us a good idea of what’s going on.

That’s it for now folks.  Thank you for reading this, and if you found it useful or you’ve got some questions about it: post a comment!

Have fun!

Valentino.

Share

Tags: , , , ,

A while ago I had been playing a bit with the new spatial data types in SQL Server 2008.  Not only can SQL Server store such data, it can also visualize it.  So I had come up with the following query:

select geometry::STPolyFromText
('POLYGON((1 1, 1.5 1.85, 2.5 1.85, 3 1, 2.5 0.15, 1.5 0.15, 1 1),
(1.5 1.5, 1.5 0.5, 1.7 0.5, 1.7 0.9, 2.3 0.9, 2.3 0.5, 2.5 0.5, 2.5 1.5, 2.3 1.5,
2.3 1.1, 1.7 1.1, 1.7 1.5, 1.5 1.5))', 0);

Running that in the Management Studio produces something like this on the Spatial results tab:

Hoegaerden - the polygon

I’m using the STPolyFromText function to produce a six-sided polygon with a letter H in the center.  To get to the result, I’m passing a bunch of XY coordinates to the function.  A line gets drawn between two coordinates following each other.

The first list of coordinates (the first list of numbers enclosed in round brackets) creates the hexagon while the second list produces the letter H.  Each list of coordinates thus represents one polygon.   The starting XY coordinate must be equal to the ending coordinate to form a closed shape.  The shape is then filled with a color.  Overlapping shapes will be filled using different colors, as shown above.

Producing these coordinates was quite interesting: to be able to calculate the angled sides of the hexagon I used the following formulas.

With S being the length of one side:

Width = 2 * S

Height = S * SQRT(3)

Note: these formulas are only applicable when the hexagon is rotated as shown above.

I needed to know these distances to be able to calculate how much I should increase the X/Y coordinates to get to the next connector.

Okay, enough theory, back to the story now.

Q: “So, why are you creating a helipad platform?”

A: No, it’s not supposed to look like a helipad platform.  The shape resembles the beer coasters for the beer called Hoegaarden.  And those coasters always have a funny drawing or comment on them.  Let’s see if I can find one that’s understandable for non-native Dutch-speakers…

(And half an hour later – turns out it’s really not easy to find one without text – luckily they’ve also created some in English!)

Hoegaarden beer coaster

Initially I was planning on writing a longer article about the spatial data types but hadn’t found the time to do it yet.  And then a couple of days ago I came across a challenge that made me think of this query and so now I’m publishing it in this post.

The challenge to which I’m referring was started by Itzik Ben-Gan and is called Geekiest Sig Challenge.  The point is to use the new SQL Server spatial data types to create a signature for yourself.  Well, a perfect description for my query!

I was happy to just get an H on there, getting those coordinates right without first putting it on paper is really a challenge :-)   And on top of that, I was never any good at drawing.  I remember in secondary school we got an assignment to draw a tree.  Any tree.  So at home I looked out the window and started drawing the tree.  I ended up with the most atrocious thing I’d ever seen (well, that may be a bit exaggerated), it could have starred in a Hitchcock movie straight away, really spiky, and probably spooky when encountered in the dark and with the right background noises playing.

Anyway, what I wanted to say, some people are just talented: check out this submission by Michael J. Swart!  No further words needed.

Right, enough geeky stuff to close the week, and remember: have fun!

(Hmm, now I’ve got something to build my favicon from…)

Valentino.

Share

Tags: , , ,

A couple of days ago I discovered a very interesting double-click feature in Excel. One that probably already exists for ages – the oldest version that I was able to try it out on is Excel 2002 and it worked – but due to being used to other habits I just never found out about it.  Until now.

When a cell (or a range of cells) is selected, you see a thicker black border around the selection and the bottom-right corner has got a small square attached.

One cell selected in Excel

You can drag that squared corner down to get copy-like behaviour. More precisely, when one cell is selected and you drag it down then the value of the selected cell gets copied over into the cells further down, up until the cell where you stop dragging.

The effect of dragging the squared corner with one cell selected

Note: the same effect also works horizontally but in real-life circumstances you probably won’t need it much.

When multiple cells are selected and you drag the bottom-right corner down, Excel will apply some logic to continue the series that it possibly detects.

For instance if you have two cells selected with the values 2 and 4, the next cells will get 6, 8, and so on. Or when the first cell contains 2010/10/30 and the second 2010/10/25, the next cells will get date values going down by 5 days per cell.

Excel's Auto Fill with two rows and two cells per row selected

If you don’t like the way the series gets applied, there’s a dropdown poping up at the bottom-right of the new selection at the moment that you stop dragging. In that list you get several different Auto Fill Options, depending on the data type of your selected cells.

The Auto Fill Options

I’m sure this is no news to you so far.

But do you know what happens when you just double-click that small bottom-right corner instead of dragging it?

I didn’t, until I just tried it out this past week. The reason that I tried this was because I needed a formula copied down in about 20,000 rows (hey, I’m a data guy, remember?) and I didn’t want to waste my time waiting for Excel to scroll down just to the bottom of the list while dragging the corner. So I double-clicked and there came the discovery of the week! It applied the same function as what you get when you drag the corner down, all the way down to the last row of data! Isn’t that great? From now on I think I’ll always just double-click instead of drag, much faster!

The Auto Fill effect after double-clicking the small squared corner (I still wonder what name they've given this feature - the Auto Fill Corner possibly?)

And you even get the same popup to select another Fill Option.

Have Fun!

Valentino.

Share

Tags:

If you’re using Google Reader and you’ve got a WordPress blog then I’ve got a really useful tip for you!

Especially if you want to save time maintaining all those links in your blogrolls.

You can synchronize your blogrolls on your blog with folders in your Google Reader. Which means zero maintenance for your blogrolls: they’re automatically updated when you update your Reader subscriptions.

I actually found this solution on another site so I’ll just point you there: http://www.adashofbitters.com/2009/01/02/blogroll-google-reader-wordpress-easy/

You can see it in action in my sidebar on the right. Several of those link lists are being fed from Google Reader, such as the one called "SQL Blogs @Microsoft".

Happy blogging!

Valentino.

Share

Tags: , ,

We’ve finally gotten a built-in tool in Windows to capture those screenshots for blogging/documentation/whatever purposes.  And it has been given the magical, oh-so-logical name of Snipping Tool!

Update: I’m aware that Vista already contained this tool.  But I’m one of those guys who managed to stay away from Vista so to me it’s new :-)

Let’s see if I can capture what it looks like by using the tool itself.  Hmm, I can’t…  Which is probably quite logical as it shouldn’t get in the way when you want to capture a screenshot of something.  What you want to see then is anything but the actual tool used to do it, right??

I’ll go for the old-fashioned way then: ALT + Print Screen (it captures the active window).

Windows 7 Snipping Tool - Capture those screens!

In that screenshot I’ve demonstrated a couple of its features.  It comes with a Pen that you can customize a bit into several colors and thickness.  You’ve also got a marker tool called Highlighter – that’s the yellow part.  And there’s an Eraser tool to remove any markings or pen editions you’ve made previously.

Another feature that’s not shown but that’s really useful is that the screenshots are automatically copied to the clipboard.  But you can switch that off through the options if you don’t like it.

Snipping Tool Options

I would have hoped that a couple more features had been included, such as the ability to draw arrows without needing to use a freehand tool (as shown in screenshot above), and a Rectangle/Ellipse tool for some extra markings.

Anyway, what this means as far as I am concerned is that I no longer need to install my favorite screen capture tool (I’ve used a couple over the years but the last one was Screen Hunter).  But for editing some screen captures I’ll still need to resort to my favorite image editor (paint.net).

Where is it located? Well, Start Button > Snipping Tool.  At least, that’s where I found it in my Windows 7 Enterprise edition.

What’s the hotkey? It’s CTRL + Print Screen.

How do you use the hotkey?? From the moment that you start up the application, it wants to make a screen capture.  What this means is that your mouse pointer changes into a crosshair whenever it goes outside the Snipping Tool window.  All you now need to do is hit the ESC button.  Then switch to the application that you’d like to capture, possibly opening up a menu.  With everything in place just as you want it captured, hit CTRL + Print Screen.  There’s your crosshair again.

Have fun!

Valentino.

Share

Tags:

« Older entries

© 2008-2017 BI: Beer Intelligence? All Rights Reserved