SSIS Package Extract from MSDB

A question came up on StackOverflow of how someone can retrieve their packages from the MSDB. In this case, their local development went south and they only had the packages in production. Let this be an object lesson in using version control.

To extract packages from the MSDB, you must first identify where in the msdb they exist. For that, you can query sysssispackagefolders and sysssispackages or you can just use my query SSIS Package Query

Armed with that query, the column of interest is the PackagePath column. Couple that with dtutil and you have an extract-o-matic for package recovery.

The base form of an extract from MSDB on localhost to the current folder in the file system would look like.

dtutil /sourceserver localhost /SQL "Package" /copy file;.\Package.dtsx

Extract-o-matic

Run this query in Text mode (ctrl-T) This query generates a series of dtutil calls which in turn extracts SSIS packages from a server.

;
WITH FOLDERS AS
(
    -- Capture root node
    SELECT
        cast(PF.foldername AS varchar(max)) AS FolderPath
    ,   PF.folderid
    ,   PF.parentfolderid
    ,   PF.foldername
    FROM
        msdb.dbo.sysssispackagefolders PF
    WHERE
        PF.parentfolderid IS NULL

    -- build recursive hierarchy
    UNION ALL
    SELECT
        cast(F.FolderPath + '\' + PF.foldername AS varchar(max)) AS FolderPath
    ,   PF.folderid
    ,   PF.parentfolderid
    ,   PF.foldername
    FROM
        msdb.dbo.sysssispackagefolders PF
        INNER JOIN
            FOLDERS F
            ON F.folderid = PF.parentfolderid
)
,   PACKAGES AS
(
    -- pull information about stored SSIS packages
    SELECT
        P.name AS PackageName
    ,   P.id AS PackageId
    ,   P.description as PackageDescription
    ,   P.folderid
    ,   P.packageFormat
    ,   P.packageType
    ,   P.vermajor
    ,   P.verminor
    ,   P.verbuild
    ,   suser_sname(P.ownersid) AS ownername
    FROM
        msdb.dbo.sysssispackages P
)
SELECT 
    -- assumes default instance and localhost
    -- use serverproperty('servername') and serverproperty('instancename') 
    -- if you need to really make this generic
    'dtutil /sourceserver ' + @@SERVERNAME + ' /SQL "'+ F.FolderPath + '\' + P.PackageName + '" /copy file;".\' + P.PackageName +'.dtsx"' AS cmd
FROM 
    FOLDERS F
    INNER JOIN
        PACKAGES P
        ON P.folderid = F.folderid
-- uncomment this if you want to filter out the 
-- native Data Collector packages
-- WHERE
--     F.FolderPath <> '\Data Collector'

For me, that generated the following code.

dtutil /sourceserver localhost /SQL "\Package" /copy file;.\Package.dtsx
dtutil /sourceserver localhost /SQL "\Data Collector\PerfCountersCollect" /copy file;.\PerfCountersCollect.dtsx
dtutil /sourceserver localhost /SQL "\Data Collector\PerfCountersUpload" /copy file;.\PerfCountersUpload.dtsx
dtutil /sourceserver localhost /SQL "\Data Collector\QueryActivityCollect" /copy file;.\QueryActivityCollect.dtsx
dtutil /sourceserver localhost /SQL "\Data Collector\QueryActivityUpload" /copy file;.\QueryActivityUpload.dtsx
dtutil /sourceserver localhost /SQL "\Data Collector\SqlTraceCollect" /copy file;.\SqlTraceCollect.dtsx
dtutil /sourceserver localhost /SQL "\Data Collector\SqlTraceUpload" /copy file;.\SqlTraceUpload.dtsx
dtutil /sourceserver localhost /SQL "\Data Collector\TSQLQueryCollect" /copy file;.\TSQLQueryCollect.dtsx
dtutil /sourceserver localhost /SQL "\Data Collector\TSQLQueryUpload" /copy file;.\TSQLQueryUpload.dtsx

Hooray, my bacon is saved and now I can put my packages into version control like a good developer.

PowerShell, dtutil, SSISDeployManifest - How to deploy SSIS packages with PowerShell

I have made no progress since 2008 on the SSIS Uploader. I still think it has merit and I hope to finish it some day. Today is not that day.

Background

At work, we are in the midst of rolling out SQL Server 2008. No R2, just vanilla SQL Server 2008 (named instance) in a side-by-side installation with the 2005 default instance. We are not in production yet but today marked the first issue we had encounted with it. We deploy SSIS packages via the dtsinstaller. It consumes a manifest file (XML) and deploys all of the specified packages into the destination location: file system or into SQL Server (msdb) the installer handles them both. We encountered the following error message today "Unable to find an entry point named 'DTSSKUIsWKGorUnder' in DLL 'dts'. (DTSInstall)." When Google turns up one hit and it's to a Connect article, that's never an encouraging sign. Labouring under the delusion that dtutil will work better than the fancy dtsinstaller, the thought struck me that I could get the benefits of a command line deploy and still incorporate the deployment manifest. For extra points, it seemed like a good reason to use PowerShell to glue it all together.

PowerShell, dtutil, SSISDeployManifest - How to deploy SSIS packages with PowerShell

The following script is my first pass at enumerating the elements of a SSISDeploymentManifest file and using dtutil to install those packages to a 2008 named SQL Server instance. Ain't that a mouthful?

# 2010-04-30
# Bill Fellows
# This PowerShell script is designed to shred an SSISDeploymentManifest
# and deploy file based SSIS packages into a SQL Server instance.
#
# Being the extreme novice with PowerShell, everything is hardcoded and 
# it assumes you have dtutil installed on the same drive as this script.
# Possible enhancements would be to look in the registry for location, 
# accept parameters, encapsulate code into a cmdlet, others as they come up

$format = """\Program Files\Microsoft SQL Server\100\DTS\Binn\dtutil.exe"" /file ""{0}"" /DestServer {1} /COPY SQL;""{2}"""

# This deploy script assumes all packages are in the same folder as the 
# manifest
$fileName = "C:\src\SSISHackAndSlash\SSISHackAndSlash\bin\Deployment\SSISHackAndSlash.SSISDeploymentManifest"

# The destination server, this should be a parameter
$destinationServer = "localhost\SQL2008"

$baseFolder = [System.IO.Path]::GetDirectoryName($fileName)

[xml] $list = Get-Content $fileName

foreach($package in $list.DTSDeploymentManifest.Package)
{
    $basePackage = [System.IO.Path]::GetFileNameWithoutExtension($package)   
    
    # This might need to be a relative path
    $fullyQualifiedPackage = [System.IO.Path]::Combine($baseFolder, $package)
    
    $cmd = [string]::Format($format, $fullyQualifiedPackage, $destinationServer, $basePackage)
 
    cmd /c $cmd
}

The script is simple enough. Given a manifest file in the form of

<?xml version="1.0"?> 
<DTSDeploymentManifest GeneratedBy="HOME\administrator" GeneratedFromProjectName="SSISHackAndSlash" GeneratedDate="2010-04-30T21:19:08.6336677-05:00" AllowConfigurationChanges="true">
   <Package>Package.dtsx</Package>
   <Package>Package2.dtsx</Package>
   <Package>Package7.dtsx</Package>
</DTSDeploymentManifest>

it will simply run the following commands

"\Program Files\Microsoft SQL Server\100\DTS\Binn\dtutil.exe" /file "C:\src\SSISHackAndSlash\SSISHackAndSlash\bin\Deployment\Package.dtsx" /DestServer localhost\SQL2008 /COPY SQL;"Package"
"\Program Files\Microsoft SQL Server\100\DTS\Binn\dtutil.exe" /file "C:\src\SSISHackAndSlash\SSISHackAndSlash\bin\Deployment\Package2.dtsx" /DestServer localhost\SQL2008 /COPY SQL;"Package2"
"\Program Files\Microsoft SQL Server\100\DTS\Binn\dtutil.exe" /file "C:\src\SSISHackAndSlash\SSISHackAndSlash\bin\Deployment\Package7.dtsx" /DestServer localhost\SQL2008 /COPY SQL;"Package7"

What I like about this solution

• Scriptable---our deploys no longer require UI interaction so RDP rights no longer required
• Uses manifest file---Our devs are trained to look at the manifest as the definitive source for SSIS deployments
• It uses PowerShell

The big drawback in my mind, is that there is something buggered up with our servers. Ultimately, this is a workaround until we resolve that issue but if it becomes our defacto deploy method, I think we'll be better off.