So, in response to Jamie Thomson’s article on extracting all SSIS packages from msdb, I also wanted to show how this can be done from within SSIS itself. It’s very straightforward, and uses a data flow component you may not be familiar with: Export Column.
First, setup four package level variables:
FilePath – string
SSISFolderFilePath – string
SSISFolderName – string
SSISFolders – object
FilePath should be set to the location where you want to store the packages. Include the trailing slash, and ensure that the location exists.
SSISFolderFilePath should be set to evaluate an expression (EvaluateAsExpression = TRUE), and its expression should be:
@[User::FilePath] + @[User::SSISFolderName]
Then create a connection manager object that points to your MSDB database where you want to extract from. If you want this to be dynamic, perhaps to archive ALL SSIS packages in your company, then this could be made a dynamic connection manager object (by using expressions) and you iterate over each server. For now, we’re only going to use one server.
Once these are setup, the rest is point and click, pretty much. Add an Execute SQL Task to the control flow, and configure it so that it returns a full resultset. See below.
The query for the Execute SQL Task is:
FROM dbo.sysssispackagefolders folders
WHERE EXISTS (SELECT NULL
FROM dbo.sysssispackages pkg
WHERE pkg.folderid = folders.folderid)
*Note – the above SQL only assumes a flat directory structure. If you have subdirectories in MSDB, you can use a recursive CTE to create a hierarchy resultset.
With the Execute SQL Task set up, you’ll have to shred the results using a Foreach Loop Container. Inside the Foreach Loop Container, we will want to use a File System Task to create directories for our packages if they do not already exist. The configuration for that is below:
Next, we have to create a data flow and attach it to the Foreach Loop Container. The full control flow should resemble the next screenshot.
In the data flow, we have three components – OLE DB Source, Derived Column, and the Export Column transformation. Connect the OLE DB source to your connection manager object, and use the following query against MSDB:
FROM dbo.sysssispackages pkg
JOIN dbo.sysssispackagefolders folder
ON pkg.folderid = folder.folderid
Then, configure the Derived Column component so that it creates one new column, FullFilePath. The following is the expression to use:
(DT_WSTR,500)(@[User::FilePath] + foldername + "\\" + name + ".dtsx")
Then configure the Export Column transformation as the following screenshot illustrates:
The final data flow should look like the following:
The only thing left to do is execute the package. Navigate to the location defined by the FilePath package variable and you should see a directory structure resembling that in MSDB with all of the SSIS packages saved.
If you wish to download the full SSIS package, here you go: http://www.ssistalk.com/ExportSSISPackages.dtsx