将部署在SQL Server上的SSIS包复制回Visual Studio 2008 [英] Copying SSIS packages deployed on SQL Server back to Visual Studio 2008

问题描述

安装了Visual Studio 2008的我的Development SSIS盒不再起作用.我试图弄清楚如何获取在生产SQL 2008 SP2服务器上运行的软件包并将其插入新服务器上的Visual Studio的新安装中.

My Development SSIS box with my Visual studio 2008 installation is not working anymore. I am trying to figure out how I can take the packages running on my production SQL 2008 SP2 server and insert them into a new installation of Visual Studio on a new server.

谢谢

推荐答案

我假设OP知道基本文件副本，但我认为他们的问题是他们已将软件包部署到MSDB中.

I'm assuming the OP is aware of a basic file copy but I believe their issue is they have the packages deployed into the MSDB.

要从MSDB中提取软件包，必须首先在msdb中标识它们存在的位置.为此，您可以查询sysssispackage文件夹和sysssispackages，也可以只使用我的查询 SSIS包裹查询

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

与该查询一起，关注的列是PackagePath列.将其与 dtutil 结合使用，您将获得程序包的extract-o-matic恢复.

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.

从本地主机上的MSDB提取到文件系统中当前文件夹的基本形式如下.

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

以文本模式(ctr-T)运行此查询.此查询生成一系列dtutil调用，这些调用又从服务器提取SSIS程序包.

Run this query in Text mode (ctr-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 localhost /SQL "'+ F.FolderPath + '\' + P.PackageName + '" /copy file;.\' + P.PackageName +'.dtsx'
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'

这篇关于将部署在SQL Server上的SSIS包复制回Visual Studio 2008的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！