Saving SSIS package from server to local computer

Question

Right now I'm able to upload an SSIS package from my local computer to SQL Server with my C# program:

Microsoft.SqlServer.Dts.Runtime.Application app;
app = new Microsoft.SqlServer.Dts.Runtime.Application();


string pkg = @"C:\Temp\myPackage.dtsx";
Package loadedPkg = app.LoadPackage(pkg, null);

app.SaveToSqlServerAs(loadedPkg, null, "myNewPackage", "myServer", null, null);

This works great. Now I want to do the opposite of this, save an SSIS package from the SQL Server to my local computer. I'm using the Microsoft documentation here: https://learn.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.dts.runtime.application?view=sqlserver-2017

It doesn't seem to have any methods to save to a computer. I see the "load" methods, but I haven't been able to get those to work. I think they just prep a package to be uploaded to the server, like in my previous example. Is this possible through the same "Application" class?

Answer 1

Try the combination of LoadFromSqlServer and SaveToDtsServer together

If I'm understanding you correctly, you want to load you package from a file to SQL Server. Then, do the reverse, to write package to a file from SQL Server. I think the magic might be in the following code snippet here:

pkgIn = app.LoadFromSqlServer("\\UsingExecuteProcess", "yourserver", null, null, null);  
app.SaveToDtsServer(pkgIn, null, @"File System\myFolder2", "yourserver");  

Here's some code I haven't tested that's a combination of the following two articles shown immediately after. Here the code, loads UsingExecuteProcess.dtsx TO SQL Server. Then, the code uses LoadFromSQLServer to get the package back into the variable pkgIn FROM SQL Server. Finally, SaveToDtsServer writes pkgIn back to the file system (again no compilations yet so there may be syntax error or two to work through--I essentially combined the code from two articles listed).

Note:

If your local computer is really over the network, you could use a UNC (\MyComputer\MyShare\MyPackage.dtsx) naming convention to save the file to your system from SQL Server over the network--instead of using a local drive letter that's only available on SQL Server.

    using System;  
    using System.Collections.Generic;  
    using System.Text;  
    using Microsoft.SqlServer.Dts.Runtime;  

    namespace LoadFromSQLServerTest  
    {  
        class Program  
        {  
            static void Main(string[] args)  
            {  
                // The variable, pkg, points to the location  
                // of the ExecuteProcess sample installed with  
                // the SSIS package samples.  
                string pkg = @"C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Package Samples\ExecuteProcess Sample\ExecuteProcess\UsingExecuteProcess.dtsx";  
                Application app = new Application();  
                Package loadedPkg = app.LoadPackage(pkg, null);  

                // Save the package to SQL Server.  
                app.SaveToSqlServer(loadedPkg, null, "yourserver", null, null);  

                // The package can now be viewed in the   
                // Microsoft SQL Server Management Studio, in the   
                // Integration Services / Stored Packages / MSDB folder,  
                // with a name of UsingExecuteProcess.  
                Package pkgIn = new Package();  
                pkgIn = app.LoadFromSqlServer("\\UsingExecuteProcess", "yourserver", null, null, null);  
                app.SaveToDtsServer(pkgIn, null, @"File System\myFolder2", "yourserver");  
                DateTime pkgCreation = pkgIn.CreationDate;  
                Console.WriteLine("Creation Date = {0}", pkgCreation);  
            }  
        }  
    }  

https://learn.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.dts.runtime.application.loadfromsqlserver?redirectedfrom=MSDN&view=sqlserver-2017#Microsoft_SqlServer_Dts_Runtime_Application_LoadFromSqlServer_System_String_System_String_System_String_System_String_Microsoft_SqlServer_Dts_Runtime_IDTSEvents_

https://learn.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.dts.runtime.application.savetodtsserver?view=sqlserver-2017

Answer 2

TECHNICALLY, it can be done through the SaveToXML method. However, for this to work, the process on the SQL Server has to have write access to a share on your destination machine, and you have to specify the share when you give the filename.

Other than that, I can't see a way to do what you are looking for. I was hoping that there was some way to get the package into an object in memory that you could manipulate - which, again technically, I guess you could do by saving the package to XML, then reading it from disk into an object, then saving the object locally. Neither of these options are really what you are looking for, but I believe that they are your only real options available.