Skip to main content

Copy SSIS packages from MS SQL SERVER 2008 R2

Copy SSIS packages from SQL Server 2008 R2 to another SQL Server 2008

First day at the new job I was asked if I knew anything about moving SSIS packages from one server to another server. The developer/dbo was dreading having to manually download and deploy the SSIS packages from the old server to the new server.

I quickly started looking for an automated, scripted solution and came across this stackoverflow post that was asking nearly the exact same question.  The only real difference was the original question was asking about  moving where as we needed to copy. (moving meant removing from old server as we nearly found out the hard way).

Below is the sql code from that post that I decided to group together on my github for sharing and for later use which by the way, I ended up reusing. I think making this into a stored procedure with a few more bells and whistles might come in handy. 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
/*
Copy SSIS packages from MSDB SQL SERVER 2008 R2 to another SQL SERVER 2008 R2.  

Requires command line utility dtutil:
    C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn\dtutil.exe
    (Path varies on verison ie. 130)
Notes: 
    Assumes default instance and localhost.
    File extraction command line
    'dtutil /sourceserver localhost /SQL "'+ F.FolderPath + '\' + P.PackageName + '" /copy file;".\' + P.PackageName +'.dtsx"' AS cmd

Credit for original base code: 
    https://dba.stackexchange.com/questions/68481/moving-ssis-packages-to-new-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 
    -- sample output required for command line usage
 -- dtutil /SQL "\Package1" /SOURCEUSER <username> /SOURCEPASSWORD <password> /DestServer <DESTINATIONSERVER> /COPY SQL;"\Package1" /DESTUSER <username> /DESTPASSWORD <password>
 -- Move Package command line
    'dtutil /SQL "'+ F.FolderPath + '\' + P.PackageName + '" /SOURCEUSER <username> /SOURCEPASSWORD <password> /DestServer <DESTINATIONSERVER> /COPY SQL;"'+ F.FolderPath + '\' + P.PackageName + '" /DESTUSER <username> /DESTPASSWORD <password>' AS cmd
FROM 
    FOLDERS F
    INNER JOIN
        PACKAGES P
        ON P.folderid = F.folderid
 WHERE
     F.FolderPath <> '\Data Collector'

You can run this and copy the results to a file editor which I found useful to then copy and replace all the usernames/passwords with our information. Running one line to test was nice before running the whole batch file.

Here is the batch file to copy to. I kept one line in order to show the the format but your results from the query above should pull all your package paths out. Remember to run this on the original SQL Server.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
REM Script to COPY SSIS packages from one SQL_SERVER to another SQL_SERVER
REM ECERVANTES 12.15.2017 
REM USAGE:
REM     Copies MSDB packages from server to server. Script must be altered to move/copy from file system. 
REM     Users SQL Server command line tool: dtutil 
REM     Must be run from origin server. 
REM Notes:  
REM     Yes I know there is no for loop but I couldn't find a way to do that with MSDB packages. 
REM     Text generated using ExtractSSISPackageInformation.sql script. Use that to apply changes. 
REM     <username> and <password> removed for security


dtutil /SQL "\<PACKAGE NAME>" /SOURCEUSER <username> /SOURCEPASSWORD <password> /DestServer <DB_SERVER> /COPY SQL;"\<PACKAGE NAME>" /DESTUSER <username> /DESTPASSWORD <password>
Labels:

Comments

Post a Comment

Popular posts from this blog

Sending SMS via code (.Net, c#)

Stream Our Mistakes EP 001 --- In this episode, we will work on sending SMS messages via code using twilio. The example said it could be done in 30 seconds but it took us closer to an hour. Browse the code, fork the repo to get ready for our next steps. https://github.com/mmdempsey/SMS-Automation Subscribe to the podcast on itunes , google play ,   stitcher | eddyizm site: http://eddyizm.com twitter: http://twitter.com/eddyizm github: https://github.com/eddyizm matt site: http://octon.io/ github: https://github.com/mmdempsey --- **Tool of the week:** https://technet.microsoft.com/en-us/sysinternals/zoomit.aspx --- **youtube live broadcast:** https://youtube.com/user/eddyizm/live Subscribe to our channel and follow my twitter feed to be notified of our next live broadcast and feel free to leave us comments and suggestions on what you want to see. ---
3 comments
Read more

Data Visualization with Python

Scatter plots with Matplotlib  I'm in the middle of taking a 6 week Data Visualization course at Code Academy  so I guess you might call this a midterm project. In this jupyter notebook project, we have use real world space data (celestial star location ) for the Orion constellation and output a 3D scatter plot. This was fun but because it is an intro course, the project didn't even get to labeling the actual star which I thought was bunk.  At the end of the project, they offer you a link  to some star data and challenge you to plot some local stars. So I decided to publish my results that I will end up turning in for the extra credit portion of the project. (code below) I picked a few stars, starting with some familiar ones, like Sirius, and started plotting it out. It took a while to get the labels on correctly, for some reason, I thought it was going to be easy but it definitely took some searching as the 3D portion of it made finding the examples far m...
Post a Comment
Read more

004 - HTML Scraping with Beautiful Soup

Stream Our Mistakes EP 004 In this episode, Matt walks us through html/web scraping using the popular python library, Beautiful Soup. Here's the code snippet from the session and links: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 # Created for Stream Our Mistakes # https://streamourmistakes.blogspot.com/ # Reference: # https://docs.python.org/3/library/urllib.request.html # https://www.crummy.com/software/BeautifulSoup/bs4/doc/ from bs4 import BeautifulSoup import urllib.request ''' # local html to play with from documentation Uncomment to enable html_doc = """ <html><head><title>The Dormouse's story</title></head> <body> <p class="title"><b>The Dormouse's story</b></p> <p class="story">Once upon a time there were three little sister...
Post a Comment
Read more