Working with Oracle and SSIS/DTS on a 64-bit windows server has been a painful experience.

Here are the steps to connect to Oracle if you’re setting up a new SSIS package.

1. Create a connection using the .net Providers\OracleClient Data Provider
2. Use the DataReader source type.  Select the Oracle connection you created in step one.
3. Use whatever destination you need to – it doesn’t matter at this point.  I typically use the OLE DB for SQL Server destinations.
4. Schedule as a job with the integration services type step.  No 32 bit DTEXEC modification needed as was the case with DTS steps that hit Oracle. 

You may hit some quirky errors with overflows or numeric data types.  It’s good to make sure you have the latest oracle patchset applied before proceeding with workarounds.

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.

Constantly using ‘Save a Copy As..’ in BIDS to save a SSIS package to the server annoys me to no end. Does MSFT actually use this crap?

DTUTIL.exe is a tool that comes in your 90\DTS\binn folder. Check this out:

C:\Program Files\Microsoft SQL Server\90\DTS\Binn>

dtutil /En SQL;subfolder\packagename;5 /Fi “C:\
Documents and Settings\me\My Documents\Visual Studio 2005\Projects\ProjectName\blah\
PackageName.dtsx” /DestServer devserver

note: For the first option (/En), the subfolder does not need to include the MSDB folder which you will see when browsing SSIS.

Paste that into your cmd window and you’ll be saving with two keystrokes. Your options may change, but with the help of the online docs, you’ll be happier.

If dtutil isn’t recognized you aren’t in the proper directory. You can also add the binn directory to your PATH variable. Find the PATH variable and add “;C:\Program Files\Microsoft SQL Server\90\DTS\Binn” to the end of the string. Note the semicolon at the start.