Creating folders in an SSIS package store and MSDB

In this recipe, we will see how to create a folder in the SSIS instance and the package store.

Getting ready

For this recipe, we will create a timestamped folder prefixed with the word QueryWorks. Feel free to replace it with your folder name by changing the variable $newfolder.

How to do it...

1. Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.

2. Add the ManagedDTS assembly as follows:

   #add ManagedDTS assembly
   Add-Type -AssemblyName "Microsoft.SqlServer.ManagedDTS, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

3. Add the following script and run:

   $server = "KERRIGAN"
   
   #create new app
   $app = New-Object ("Microsoft.SqlServer.Dts.Runtime.Application")
   $ts = Get-Date -format "yyyy-MMM-dd-hhmmtt"
   $newfolder = "QueryWorks File System $($ts)"
   
   #folder in package store
   #will appear under "Stored Packages > File System"
   if (!$app.FolderExistsOnDtsServer("\File System\$($newfolder...