How To Create A New Folder In The SSIS Catalog In T-SQL
The snippet below will create the given project folder in your target SSIS Catalog Database, but only if it does not exist yet, making the script safe for use in an incremental deployment process.
DECLARE @FolderName SYSNAME = 'MyFolder2';
DECLARE @FolderDescription NVARCHAR(1024) = 'MyFolder Description';
DECLARE @FolderID BIGINT = (SELECT folder_id FROM catalog.folders WHERE name = @FolderName);
IF @FolderID IS NULL
BEGIN
EXEC catalog.create_folder @folder_name = @FolderName, @folder_id = @FolderID OUTPUT;
EXEC catalog.set_folder_description @folder_name = @FolderName, @folder_description = @FolderDescription;
END
This is how you use it:
1) Set the @FolderName
variable to the name you want your folder to have.
2) Set the @FolderDescription
variable to something useful for your project.
3) Run the snippet against your target SSISDB
database.
Note that this will work only from SQL Server 2012
onwards.