A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Tuesday, November 22, 2011

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.

8 comments:

Saurab Rao said...

Can you please give the dtutil command to copy from dev server with a specific name to local server instance or file system. I have tried many combinations but simply gives me an error that a particular path on dev server doesn't exist.Thanks in advance.

TiePact28 said...

Here is a version that works with 2005.. .Enjoy and thanks!!!


;
WITH FOLDERS AS
(
-- Capture root node
SELECT
cast(PF.foldername AS varchar(max)) AS FolderPath
, PF.folderid
, PF.parentfolderid
, PF.foldername
FROM
msdb.[dbo].[sysdtspackagefolders90] 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].[sysdtspackagefolders90] 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].[sysdtspackages90] 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'

Unknown said...

Thanks for the script. Like the posting says the export works for SQL 2008 R2 and for some reason doesn't work when i try to export it out of SQL 2014.
Please see the error message.

Could not load package "\Outbound Interfaces\ssis_Extract_PHS_Provider_Roster" because of error 0xC0011008.
Description: Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.

Thanks,Sen.

Bill Fellows said...

Sen,
Without seeing your environment, my first guess would be that you need to ensure that you're using the correct version of dtutil for your SQL Server. The following list is for default locations of SQL Server 2014-2005 in descending order.


- C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn\dtutil.exe
- C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Binn\dtutil.exe
- C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\dtutil.exe
- C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtutil.exe
- C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtutil.exe

Unknown said...

Bill, Thank You for the quick reply. I do see the dtutil.exe on the right location for both SQL 2008 R2 and SQL 2014. The script works only partially. Some of the packages are exported and not all of them. I tried to run them for individual folders within MSDB and it is the same behavior.

Basically We are upgrading/migrating from SQL 2008 R2 to SQL 2014. I was able to export everything out of SQL 2008 R2 and imported into SQL 2014. I am done with the upgrade now and wanted to export everything out of the new 2014 server and check into TFS. I am just stuck at the last step (export packages from 2014).

Thanks,
Sen.

Unknown said...

Hi Bill, thanks for the post. Like a previous question you had posted on this page, I'm trying to COPY ALL packages from a SQL SERVER 2008 R2 box to a new SQL SERVER 2014 box. I first tried to copy a specific package from the MSDB folder on one server to the other but got this error:

C:\Program Files\Microsoft SQL Server\100\DTS\Binn>DTUTIL /SQL "\DashboardDM_Imp
ortAbsences" /DestServer "APPDEV2014" /COPY SQL;"\DashboardDM_ImportAbsences" /Q
UIET
Microsoft (R) SQL Server SSIS Package Utilities
Version 10.50.1600.1 for 64-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.

Error (0xC001404A) while checking for the existence of folder "\" on SQL Server.

Description: While trying to find a folder on SQL an OLE DB error was encountered with error code 0x80004005 (Login timeout expired).
Source:

C:\Program Files\Microsoft SQL Server\100\DTS\Binn>

I then tried to execute a script from the source server to my local file system and got this error:

C:\DTUtilCommandsToCopySSISPackagesToNewServer>dtutil /sourceserver appdev-db-1
/SQL "\ExportMunisEmployees" /copy file;".\ExportMunisEmployees.dtsx"
Microsoft (R) SQL Server SSIS Package Utilities
Version 10.50.1600.1 for 64-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.

The package \ExportMunisEmployees can not be saved to the file system since its
protection level is server storage. Use the encrypt action to change the protec
tion level.

C:\DTUtilCommandsToCopySSISPackagesToNewServer>

I keep running into these issues. I can't believe that MS does not have a COPY TASK in SSIS or SSMS that copies MULTIPLE packages from one server to another that may or may not have a different version of SQL SERVER on it.

Very exasperating. At any rate, I digress. Can you provide any insight on how to get this done simply?

Thanks.

Unknown said...

When I run the script I get back my packages and the one I am trying to copy from 1 server to another:
dtutil /sourceserver WIN-9DRARJUL1KP\MSSQL /SQL "\Maintenance Plans\Nightly Full Backups" /copy file;".\Nightly Full Backups.dtsx"
but when I run that command I get the following error

This application requires one of the components: Integration Services, Business Intelligence Studio, Management Tools -
Basic or Database Engine to be installed by SQL Server 2014 Standard, Enterprise, Developer, Business Intelligence, or E
valuation Edition. To install a component, run SQL Server Setup and select the component name.

I have scripted a complete build of my SQL servers (Web Ed) and scripted the SQL dbs and user creation and all I have left is to script the my 5 maintenance plans
thanks

Bill Fellows said...

Hi Dan,

As I understand it, dtutil.exe is part of the Integration Services Service installation for SQL Server. The executables (dtutil, dtexec) perform a licensing check to verify they can run. The 2014 feature page specifies that SSIS only works with Enterprise/BI/Standard which is why your export is failing.

Best you can do is recreate the maintenance plans on the target servers or look at alternatives for your backup/defrag etc. Ola's Scripts or MinonWare are both solid, free offerings.