So you have a mess of packages on your server and you’d like to know what they connect to without going into each package? Yeah, me too.

Create a visual studio c# console application with Visual Studio (express will probably work).

Paste this code in:

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

namespace SSISPackageInspector
{
 

    public static class CsvWriter
        {
           private static StreamWriter _writer = new StreamWriter(@"c:\temp\ssis_list.csv");

           public static  StreamWriter Writer 
           {
              get { return _writer; }
           }
        }




  class Program
  {

      static void Main(string[] args)
      {
          string outfile = null;
          string folder = null;
          string server = null;


          if (args.Length == 0)
          {
              System.Console.WriteLine("Please enter a numeric argument.");
              Console.Read();
              return;
          }
          else
          {
              int i = 0;
              foreach(string s in args)
              {
                  if (args[i].Substring(0,2)==@"\o")
                  {
                      outfile = args[i].Substring(2);
                  }

                  if (args[i].Substring(0, 2) == @"\f")
                  {
                      folder = args[i].Substring(2);
                  }

                  if (args[i].Substring(0, 2) == @"\s")
                  {
                      server = args[i].Substring(2);
                  }

                  i++;
              }

          }


          if (outfile != null)
          {

             
              
          }
          //Look up All package names for a server
          PrintDirectoryContents(folder,server);

         if (outfile != null)
          {
          // CsvWriter.Close();
          }
          Console.Read();
      }


      static void PrintDirectoryContents(string path, string server)
      {
          
           
          string sqlFolder;
          string sqlServer;

          Application ssisApplication;
          PackageInfos sqlPackages;

          sqlServer = server;

          ssisApplication = new Application();

          
          sqlFolder = path;
          sqlPackages = ssisApplication.GetDtsServerPackageInfos(sqlFolder, sqlServer);
          if (sqlPackages.Count > 0)
          {
              Console.WriteLine("Packages stored in " + path);
              foreach (PackageInfo sqlPackage in sqlPackages)
              {
                  Console.WriteLine(sqlPackage.Name);
                  Console.WriteLine("  " + sqlPackage.Flags);

                  if (sqlPackage.Flags.ToString() == "Package")
                  {
                      GetConnections(@"\" + sqlFolder,sqlPackage.Name,server);

                  }

                  if (sqlPackage.Flags.ToString() == "Folder")
                  {
                      PrintDirectoryContents(path + @"\" + sqlPackage.Name,server);
                  }

              }
          }
      }
      
      
    static void GetConnections(string path,string pkgname,string server)
    {

      // Create a package, and retrieve its connections.
      Application mApplication = new Application();

      //Package pkg = mApplication.LoadPackage(@"C:\temp\DBA_Repository_Load.dtsx", null);
      Package pkg = mApplication.LoadFromDtsServer(path + @"\" + pkgname , server, null);
      Connections conns = pkg.Connections;

      //Create the Enumerator for each connection
      ConnectionEnumerator myEnumerator = conns.GetEnumerator();
      Console.WriteLine("------" + pkg.Name.ToString() + "----------");
      
      while ((myEnumerator.MoveNext()) && (myEnumerator.Current != null))
      {
          string myLine = null;
          myLine += server + "," + path + "," + pkg.Name.ToString() + "," + myEnumerator.Current.Name;

          Regex r = new Regex("([^=;]*)=([^;]*)");

          MatchCollection mc = r.Matches(myEnumerator.Current.ConnectionString);
          
          Console.WriteLine("Conn Name:" + myEnumerator.Current.Name.ToString());
           
          string s = myEnumerator.Current.ConnectionString.ToString();
          if (s.Length > 0)
          {
              if (s.Substring(0, 2) == @"\\")
              {
                  Console.WriteLine("    " + myEnumerator.Current.ConnectionString);
                  myLine += "," + myEnumerator.Current.ConnectionString;
                  
              }
          }

          foreach (Match m in mc)
          {
              if (m.Groups[1].ToString() == "Data Source" || m.Groups[1].ToString() == "Initial Catalog")
              {
                  if (m.Groups[1].ToString() == "Data Source")
                  {
                      Console.WriteLine("  Server:" + m.Groups[2]);
                      myLine += "," + m.Groups[2];
                  }
              }

              if (m.Groups[1].ToString() == "Initial Catalog")
              {
                  Console.WriteLine("    Database:" + m.Groups[2]);
                  myLine += "," + m.Groups[2];
              }
          }

          CsvWriter.Writer.WriteLine(myLine);
          //Console.WriteLine(myLine);
          

      }

      Console.WriteLine("------End Package-------");
      
      
    }
  }
  
}

Go to project > properties > debug > start options

Enter these parameters

\sMyServer \fmsdb\mydirectory \oc:\temp\ssis_list.csv

The first is your SSIS server you’d like to connect to.

The second is your ssis directory to process – you can just use msdb and do the whole shebang.

The final is your output file. Duh.

You can now press play in VS and see the console window spin by. Look for your file in the directory you specified. You can do all kinds of spiffy stuff to packages using .net.

More information on running and managing packages programmatically is available from the good folks at MSFT.