You can create package folders through SSMS, but you may also wish to do this as part of a deployment process or installation. In this case you will want programmatic method for managing folders, so how can this be done?

The short answer is, go and look at the table msdb.dbo. sysdtspackagefolders90. This where folder information is stored, using a simple parent and child hierarchy format.

To add new folder directly we just insert into the table -

   INSERT INTO dbo.sysdtspackagefolders90 (
       folderid
      ,parentfolderid
      ,foldername)
   VALUES (
       NEWID()                 -- New GUID for our new folder
      ,<<Parent Folder GUID>>  -- Lookup the parent folder GUID if a child or another folder, or use the root GUID 00000000-0000-0000-0000-000000000000
      ,<<Folder Name>>)        -- New folder name

There are also some stored procedures -

• sp_dts_addfolder
• sp_dts_deletefolder
• sp_dts_getfolder
• sp_dts_listfolders
• sp_dts_renamefolder

To add a new folder to the root we could call the sp_dts_addfolder to stored procedure -

    EXEC msdb.dbo.sp_dts_addfolder
       @parentfolderid     = '00000000-0000-0000-0000-000000000000' -- Root GUID
      ,@name               = 'New Folder Name

The stored procedures wrap very simple SQL statements, but provide a level of security, as they check the role membership of the user, and do not require permissions to perform direct table modifications.