At the Microsoft TechEd conference in Orlando earlier this month I mentioned my PacMan project on CodePlex to several different audiences, including during each of my breakout sessions. PacMan is my "SSIS Package Manager" utility that I've built in C# to make my own life easier, but enough people expressed interest in it that I shared the utility online with hopes that it would make their lives less painful as well.
Well, although I may have succeeded to some extent, the very nature of PacMan probably limits its analgesic potential. This is because PacMan is:
- A "rough and dirty" development utility. This is code that I, as a developer, wrote for myself, as a developer, to use. This means that I was focused on solving short-term tactical goals, and not on building a general-purpose reusable framework for solving longer-term strategic goals. To put this another way, I expected to have to go in to PacMan and write a little code any time I wanted it to do something; I didn't have the time, energy or inclination to write all of that code up front.
- Largely undocumented. Other than the source code itself, there is little help for developers who want to start using PacMan for their own purposes.
Sadly, my schedule is highly unlikely to allow me to address the first point at any time in the foreseeable future. PacMan will likely always remain a "rough and dirty" utility, because the same work that keeps it valuable to me also keeps me too busy to refactor and refine it into something better.
But the second bullet is something I am likely able to do something about sooner rather than later. In fact, I plan on doing a little something about it today. Right now. Right Here:
PacMan Overview:
The whole point of PacMan is to perform batch operations on groups of SSIS packages. That's it. Because SSIS does not provide any built-in features for working with multiple packages at one time, this was something that I felt was sorely needed. Specifically, I needed a way to add a new variable to close to 100 packages. Obviously manually updating the packages wasn't an option, so PacMan was born.
The PacMan utility is implemented in a Visual Studio solution with two projects: a Class Library (DLL) Components project and a Windows Forms UI project:
The Components project implements a small set of classes that encapsulate access to objects in the SSIS .NET object model. The PackageUtil and PackageCollectionUtil classes are the two most significant ones, as we'll see later on.
PacMan UI:
The PacMan UI is exceptionally simple.[1] At the top of the form there are four options for selecting the scope of operations for whatever work is going to be performed - a single package, a single Visual Studio project, a Visual Studio solution containing one or more projects, or a file system folder containing multiple subfolders and packages. At the bottom of the form there is a set of tabs; each tab contains data entry controls for initiating a specific operation that will be performed on the packages that are in scope.
PacMan Components:
As mentioned above, the two main classes in the Components project are the PackageUtil and PackageCollectionUtil classes.
The PackageUtil class is essentially a thin wrapper around a Microsoft.SqlServer.Dts.Runtime.Package object. The PackageUtil class exposes a Package object through its SsisPackage property, and also exposes a set of properties and methods that make manipulating the package more straightforward.
The PackageCollectionUtil class is a List of PackageUtil objects, along with a set of properties and methods for manipulating the packages in the List.
When an operation scope is selected through the PacMan UI, an instance of the PackageCollectionUtil class is created and stored in the class-level packages variable which is in scope and available anywhere within the PacMan UI.
Most interesting scenarios in PacMan revolve around enumerating the packages collection and doing something[2] with each of the packages it contains.
Using PacMan:
The basic pattern of using PacMan goes something like this:
- Get the PacMan code from CodePlex and open it in Visual Studio.
- Add your own code to PacMan. You can do this either by adding a new tab to the UI or reusing an existing tab. There is an existing "Dev Workspace" tab that I use for experimenting or for one-off efforts when I don't want to be bothered building a UI for what I'm working on.
- Feel good about accomplishing so much with so little effort.
Ok, so it may not be quite that simple all the time, but that's all I can think of right now. The nice thing is that the packages collection takes care of most of the hard work - all you need to do is worry about working on a single package at a time and PacMan does the rest of the work.
PacMan Use Case Example 1:
A typical example of using PacMan may look like this. The code below is used to rename a connection manager in all selected packages:
private void buttonSample_Click(object sender, EventArgs e)
{
string oldName = "LocalHost.AdventureWorksDW";
string newName = "AWDW";
foreach (PackageUtil p in packages)
{
if (p.SsisPackage.Connections.Contains(oldName))
{
p.SsisPackage.Connections[oldName].Name = newName;
}
}
packages.Save();
}
It doesn't get much simpler than that, does it? Imagine trying to reliably rename objects across a project or solution that contained dozens or hundreds of packages.
PacMan Use Case Example 2:
Some operations may require more code that what was shown above. For example, I recently needed to review a set of several hundred packages to ensure that there were no duplicate package IDs. To do this I updated the PackageCollectionUtil class to add a GetPackageIDs method that returns a SortedDictionary collection of the package IDs and the names of the packages that use them. The code looks like this:
public SortedDictionary<string, List<string>> GetPackageIDs()
{
SortedDictionary<string, List<string>> ids =
new SortedDictionary<string, List<string>>();
foreach (PackageUtil package in this)
{
string id = package.SsisPackage.ID;
if (!ids.ContainsKey(id))
{
// New key - add a new dictionary
ids.Add(id, new List<string>());
}
// either way, add the package path
ids[id].Add(package.PackageFilePath);
}
return ids;
}
Then, in the UI code, I could simply call this method and update a TreeView to display the results to the user:
private void buttonEnumerateIDs_Click(object sender, EventArgs e)
{
if (packages != null)
{
SortedDictionary<string, List<string>> ids =
packages.GetPackageIDs();
BuildPackageIdTreeView(ids);
}
}
private void BuildPackageIdTreeView(SortedDictionary<string, List<string>> packageIDs)
{
treeViewPackageIDs.Nodes.Clear();
foreach (KeyValuePair<string, List<string>> id in packageIDs)
{
if (!checkBoxShowOnlyDuplicates.Checked || id.Value.Count > 1)
{
// add a node for the ID
TreeNode newNode = treeViewPackageIDs.Nodes.Add(id.Key);
// add a child node for each package
foreach (string packagePath in id.Value)
{
newNode.Nodes.Add(packagePath);
}
newNode.Expand();
}
}
}
Summary:
As you can see, PacMan provides a framework for developers to more easily perform operations on groups of packages. Those developers will still need to write code, but the scope and complexity of the code should be significantly reduced.
Please expect to see a few more PacMan-focused posts in the days and weeks ahead. There are some features I showed off at TechEd that can probably use some additional explanation, so now that I've laid the framework for further discussion, I can start work on those posts.
In the meantime, if you have any questions, comments or suggestions on PacMan, please feel free to post them here or to the discussion forum on the PacMan site on CodePlex. I can't guarantee that I'll respond to each one in a timely manner, but I'll do my best. Enjoy!
[1] I was going to write "ugly" here, because I know what it looks like. A UI designer I'm not.
[2] Yes, I suppose this goes without saying, as doing nothing is not a particularly interesting scenario, but the "something" in question up to the developer.
