where could I find deployed SSIS package?

Question

After I deploy a .dtsx SSIS package is there any query or C# code i could run to get it's bath on the drive or could I retrieve the deployed .dtsx file.

Answer 1

As the package is stored in MSDB so Right Click on the SSIS package placed in SQL Server and then click Run Package . You will get a dialogue box

Click on the Connection Managers tab and then change the connetion strings for individual connection.

Else

if SSIS package is created using xml config file then click the configuration tab and open the config file to know the details of package

Update 1:-

1. Assigning values to SSIS package variables

Add this namespace Microsoft.SqlServer.Dts.Runtime;

Application app = new Application();
Package package = null;
package = app.LoadPackage(deployed ssis package path,null) //Load DTSX path
//Access the SSIS variables
pkg.Connections["sConn"].ConnectionString = strSourceConn;
pkg.Connection["dConn"].ConnectionString = strDestConn;

2.Reading XML

Underlying code for every dtsx package is xml .So you can load the XML using C# and then iterate through each node and find the information .

3.Getting information from dtsx.config file .

As i said above the ssis package may have a xml config file which stores all the information.This xml file is much easier to read in C# then reading the entire DTSX package mentioned in point 2

4.If package configuration is stored in sql server then use the below code .The code is taken from Collecting Information of SSIS package

    SELECT    Props.Prop.query('.') as PropXml,
    Props.Prop.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";
    string(./@p1:Name)','nvarchar(max)')  as PropName,
    Props.Prop.value('.', 'nvarchar(max)') as PropValue
    FROM    (
    SELECT    CAST(pkgblob.BulkColumn AS XML) pkgXML
    FROM    OPENROWSET(bulk 'C:\tmp\MyPkg.dtsx',single_blob) AS pkgblob
    ) t
    CROSS    APPLY pkgXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
                    /DTS:Executable/DTS:Property') Props(Prop)

Conclusion :-

If the SSIS package is created using config file . Locate where it is stored . If it is stored in XML configuration type then load that XML file in C# and search for the nodes . If the ssis data is stored in sql server then run the above sql and get all the information . The sql query returns you the package details in xml type which you can read in C# and get the desired information

Update 2:-

If SSIS package is deployed in sql server then the below code will help you retrieve the package details using C#

public class SSISDetails
{
    public string PackageData { get; set; }
    public string PackageName { get; set; }
}

  string storedProc = string.Empty;
        List<SSISDetails> _pkgcol = new List<SSISDetails>();
        string connectionString = "server=localhost;Integrated Security=SSPI";
        using (var conn = new SqlConnection(connectionString))
        using (var command = new SqlCommand("SSISDetails", conn)
        {
            CommandType = CommandType.StoredProcedure
        })
        {

            conn.Open();
            SqlDataReader reader = command.ExecuteReader();

                while (reader.Read ())
                {
                    _pkgcol.Add(new SSISDetails() 
                    {
                       //Please don't write the code like the one below accessing 
                       //columns  using index .
                        PackageName =reader[2].ToString (),
                        PackageData =reader[10].ToString ()
                    });

                }

            conn.Close();
        }

        foreach (var item in _pkgcol.Where (a=>a.PackageName =="YourPackageName") )
        {
            //read the item.PackageData and using Linq to xml retrieve the nodes which
            //you want
        }

The Stored Proc which retrives the information is :- The below sql code gives the package name ,path,description and package data in xml format

 Create Procedure SSISDetails
 as 
 begin
 with ChildFolders
 as
 (
   select PARENT.parentfolderid, PARENT.folderid, PARENT.foldername,
    cast('' as sysname) as RootFolder,
    cast(PARENT.foldername as varchar(max)) as FullPath,
    0 as Lvl
   from msdb.dbo.sysssispackagefolders PARENT
   where PARENT.parentfolderid is null
   UNION ALL
   select CHILD.parentfolderid, CHILD.folderid, CHILD.foldername,
   case ChildFolders.Lvl
        when 0 then CHILD.foldername
        else ChildFolders.RootFolder
    end as RootFolder,
    cast(ChildFolders.FullPath + '/' + CHILD.foldername as varchar(max))
        as FullPath,
    ChildFolders.Lvl + 1 as Lvl
   from msdb.dbo.sysssispackagefolders CHILD
   inner join ChildFolders on ChildFolders.folderid = CHILD.parentfolderid
   )
   select F.RootFolder, F.FullPath, P.name as PackageName,
   P.description as PackageDescription, P.packageformat, P.packagetype,
   P.vermajor, P.verminor, P.verbuild, P.vercomments,
   cast(cast(P.packagedata as varbinary(max)) as xml) as PackageData
   from ChildFolders F
   inner join msdb.dbo.sysssispackages P on P.folderid = F.folderid
   order by F.FullPath asc, P.name asc;

end

Answer 2

You can import it through Visual Studio (or Business Intelligence Development Studio). Create a new SSIS project, right-click on packages, choose import and connect to the SQL Server that the package is stored on.

Answer 3

The default package location if you deploy to file system is : C:\Program Files\Microsoft SQL Server\100\DTS\Packages (SQL 2008) or C:\Program Files\Microsoft SQL Server\90\DTS\Packages (SQL 2005).

If you deploy to sql server, it's stored in the msdb database, so you can access it from SQL Server Management Studio.

1. Select 'Integration Services' in the connection option instead of 'Database Engine'.
2. Expand Stored Packages - Expand MSDB and search for your package (With the same name which you used during saving the package.)