Parse SSIS package

Question

I have several .dtsx packages in the folder on file system.
I try to extract additional information from the package with next script:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts;

namespace ImportDataFromPackage
{
    class Program
    {
        static void Main(string[] args)
        {
            //  The pkg variable points to a package
            //  installed with the SSIS samples.
            string pkg = @"D:\MyFolder\Ryder_project\testpackage\ACS\ACS_Available_Months.dtsx";

            Application app = new Application();
            Package p1 = app.LoadPackage(pkg, null);
            p1.Description = "CalculatedColumns package";

            app.SaveToDtsServer(p1, null, @"File System\myp1Package", "YOURSERVER");
            PackageInfos pInfos = app.GetDtsServerPackageInfos(@"File System", "YOURSERVER");
            foreach (PackageInfo pinfo in pInfos)
            {
                Console.WriteLine("Package Information");
                Console.WriteLine("CreationDate:        {0}", pinfo.CreationDate);
                Console.WriteLine("Description:         {0}", pinfo.Description);
                Console.WriteLine("Flags:               {0}", pinfo.Flags);
                Console.WriteLine("Folder:              {0}", pinfo.Folder);
                Console.WriteLine("Name:                {0}", pinfo.Name);
                Console.WriteLine("PackageDataSize:     {0}", pinfo.PackageDataSize);
                Console.WriteLine("PackageGuid:         {0}", pinfo.PackageGuid);
                Console.WriteLine("VersionBuild:        {0}", pinfo.VersionBuild);
                Console.WriteLine("VersionComments      {0}", pinfo.VersionComments);
                Console.WriteLine("VersionGUID          {0}", pinfo.VersionGUID);
                Console.WriteLine("VersionMajor         {0}", pinfo.VersionMajor);
                Console.WriteLine("VersionMinor         {0}", pinfo.VersionMinor);
                Console.WriteLine();
            }
        }
    }


}

and I get next error:

Please help me to resolve the problem.

Is the package actually located in the D:\MyFolder location on the server? Or is that local? — Jacob H, Jun 06 '18 at 15:23
So I assume you are running these packages from the server, which means it will be looking at the local D:\ drive there. — Jacob H, Jun 06 '18 at 18:05

Hadi · Answer 1 · 2018-06-07T07:14:01.293

2

SSIS packages .dtsx files are Xml files, so you can use XML Parser and other techniques, Regular Expressions to get its informations. You can achieve this using SQL, .Net and other technologies.

You can follow my detailed answer to the following question:

Automate Version number Retrieval from .Dtsx files

Also if you are looking for a reverse engineering you can check the following link:

Reverse engineering SSIS package using C#

edited Jun 07 '18 at 07:14

answered Jun 06 '18 at 22:46

Hadi

36,233
13
65
124

1

Nice scripts. Thanks a lot. Could you be so kind to tell me how I can get from .dtsx package (from Connection manager: refId)? – Andriy Jun 07 '18 at 06:53
1

@Andrey your always welcomed :) i really don't know how to retrieve it. It needs some experiments to get it. You must know its pattern to search for it using Regex or you must know its path to get it using xml parsing. – Hadi Jun 07 '18 at 07:02
1

ok. I just thought you know how to work with parent-child relationships using regex or xml parsing) – Andriy Jun 07 '18 at 07:05
Good!! It will be an added value to the scripts i provided. – Hadi Jun 07 '18 at 07:16
mb, we can connect in skype for a while to resolve this question? – Andriy Jun 07 '18 at 07:35

Parse SSIS package

1 Answers1

Linked