none
Delete Packages in MSDB Folder Store

    Question

  • I have a folder in MSDB for storing SSIS packages. There are about two hundred plus packages. I now want to delete that folder, but apparently I can't delete the folder until all packages/folders in that top folder are empty.

    The real pain is that there is no way that I can see to select all packages and then delete them - it has to be done one by one!

    Is there NO way to do a "mass" delete???

    This is just basic functionality!

    Thanks!


    A. M. Robinson

    Friday, September 06, 2013 3:00 PM

Answers

  • Let's say I  have two folders in an MSDB database/store residing on one server: TEST and REAL.

    I test in TEST and need to delete the TEST folder later.

    Is that so unheard of?

    And frankly...if a mass delete is so very very rare, then again, why is it that you can mass delete database objects - tables, stored procedures, views, etc., why would a large complex enterprise want to mass delete stored procedures? Tables? Views? And do you really have 30-45 minutes to sit and delete packages one at a time?

    Or there is just no need anymore for 20-30-50 packages anymore. Maybe 100. System redesign or what have you. That's never happened before? They've been rendered useless. You're going to honestly be okay with deleting them all one by one?

    And if a plain old user in Windows user had to delete single files one at a time, they would probably pretty annoyed. I know I would be...


    A. M. Robinson


    • Edited by ansonee Sunday, September 22, 2013 7:58 AM more
    • Marked as answer by ansonee Sunday, September 22, 2013 7:59 AM
    Sunday, September 22, 2013 7:39 AM

All replies

  • I am thinking you can script that via DTUtil.exe

    You can get the list of package names from querying msdb.dbo.sysdtspackages90 (SQL Server 2005).

    Then crate a BAT that has the dtutil removing each entry.

    The following link has more on using DTUtil http://www.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/63263/


    Arthur My Blog


    Friday, September 06, 2013 3:08 PM
    Moderator
  • That's a fine solution, but it seems like a lot of work to just to be able to simply delete SSIS packages from the package store. In my case, a script would suffice for deleting everything in a folder.

    What if someone want to delete maybe 25 out of 75 packages? Writing a script in that case would be pointless. The user would have to delete one at a time. What if there were 500 packages and someone only wanted to delete 250 of those?

    I just find it bizarre that I'm able to mass delete procedures, tables, views, etc. but I can't do the same for SSIS packages.

    Seems like a design flaw to me...


    A. M. Robinson


    • Edited by ansonee Friday, September 20, 2013 9:30 PM new info
    Friday, September 20, 2013 9:28 PM
  • It is very, very rare that such a large, complex deletion would be necessary.

    Under what circumstances a business would go under such a transformation?

    In my opinion, it is not a big deal to delete 250 packages manually. lt'll probably take 30 min, but doable.


    Arthur My Blog

    Sunday, September 22, 2013 2:03 AM
    Moderator
  • Let's say I  have two folders in an MSDB database/store residing on one server: TEST and REAL.

    I test in TEST and need to delete the TEST folder later.

    Is that so unheard of?

    And frankly...if a mass delete is so very very rare, then again, why is it that you can mass delete database objects - tables, stored procedures, views, etc., why would a large complex enterprise want to mass delete stored procedures? Tables? Views? And do you really have 30-45 minutes to sit and delete packages one at a time?

    Or there is just no need anymore for 20-30-50 packages anymore. Maybe 100. System redesign or what have you. That's never happened before? They've been rendered useless. You're going to honestly be okay with deleting them all one by one?

    And if a plain old user in Windows user had to delete single files one at a time, they would probably pretty annoyed. I know I would be...


    A. M. Robinson


    • Edited by ansonee Sunday, September 22, 2013 7:58 AM more
    • Marked as answer by ansonee Sunday, September 22, 2013 7:59 AM
    Sunday, September 22, 2013 7:39 AM