0

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

pramodtech
  • 6,300
  • 18
  • 72
  • 111
jcustance
  • 3
  • 1
  • 2
  • You say the packages are running on your server. But are they also stored in the server, as opposed to disk? – Gus Cavalcanti Nov 19 '11 at 05:39
  • Agree with William. @jcustance you just need to copy your .dtsx files from your local drive and import into new project in VS. – pramodtech Nov 19 '11 at 07:16

2 Answers2

4

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.

William Salzman
  • 6,396
  • 2
  • 33
  • 49
3

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'
billinkc
  • 59,250
  • 9
  • 102
  • 159
  • 1
    And of course, let this be a lesson for using version control software. Plenty of free and pay options out there. – billinkc Nov 19 '11 at 20:53
  • thanks for your help billinkc. vc software is great unless someone sets it up on the same server were the development is being done and the system crashes – jcustance Nov 21 '11 at 15:51
  • Good catch billinkc! I read this as just not knowing you could simply copy back the dtsx files from a file system deploy. I missed that the packages were stored in server storage. (Blinders on my part, as my last two workplaces have both used file system deployment). We tend to see what we most use as what everyone does. Wish I could upvote you more than I have, you deserve the votes I got on this question. – William Salzman Nov 25 '11 at 14:59