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.

Advertisements

How To Import SQLPS While Keeping The Current PowerShell Provider

One of the very few annoyances of using the SQL PowerShell Provider becomes apparent when you import its module. Your current location, probably a working folder somewhere in your file system, will get overridden by the default location of the new provider.

To avoid this behaviour, you can include a function like the one below in your scripts.
This will hold on to your current location, import the module and then reset your location to what it was before.

function Import-SQLPS
{
    $Current = Get-Location
    Import-Module SQLPS -DisableNameChecking
    Set-Location $Current
}

Index: PowerShell HowTo’s