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.
