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.
