Create MSDB Folders Through Code
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.
