Here at work, we need to import from DBase files every now and again. DBase is a strange breed of database if you’ve been working in SQL. To import from SQL, you use a OLE DB connection and then pick the folder that contains your DBase file – which is actually just one table. http://msdn.microsoft.com/en-us/library/aa337084.aspx
I seem to remember running into a problem with the length of the DBase file name a year or so ago, so if you’re still having problems try shortening the name.
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.
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.