将部署在 SQL Server 上的 SSIS 包复制回 Visual Studio 2008

发布于 2024-12-16 14:41:31 字数 125 浏览 0 评论 0原文

我的 Visual studio 2008 安装的开发 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.

thanks

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

溺渁∝ 2024-12-23 14:41:31

包只是 xml 文件。只需将文件复制到本地,创建一个新的空项目,然后使用解决方案资源管理器中的“添加现有”对话框选项将 *.dtsx 文件导入到项目中。

Packages are just xml files. Just copy the files local, create a new empty project and then import the *.dtsx files into the project by using the Add Existing dialog choice from the Solution explorer.

永不分离 2024-12-23 14:41:31

我假设 OP 知道基本文件副本,但我相信他们的问题是他们将包部署到 MSDB 中。

要从 MSDB 中提取包,您必须首先确定它们在 msdb 中的位置。为此,您可以查询 sysssispackagefolders 和 sysssispackages 或者您可以使用我的查询 SSIS包查询

有了该查询,感兴趣的列就是 PackagePath 列。将其与 dtutil 结合起来,你就有了一个 extract-o-matic 包恢复。

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

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

Extract-o-matic

在文本模式下运行此查询 (ctr-T) 此查询生成一系列 dtutil 调用,其中依次从服务器提取 SSIS 包。

;
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'

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.

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 (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'
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文