Posts tagged ‘SSIS API’

Batch Destination and the Merge Destination

After I created the Batch Destination for my presentation at the MS BI Conference, I was talking with Matt Masson, who let me know that they’d been working on a MERGE Destination sample component, and would hopefully be releasing it soon. It’s out now on the CodePlex site for the Integration Services team samples. I’ve taken a look at it, and it is very nicely done.

What’s the difference between the two? The MERGE Destination has a much nicer interface, that lets you graphically build a MERGE statement. And, of course it uses the MERGE statement to perform updates and inserts in a single operation. This is nice, but limits you to using a SQL Server 2008 database as a destination. The Batch Destination executes any SQL command you give it, so it can be used on SQL Server 2005 or 2008.

I haven’t done a performance comparison between the two, so I’m not positive which one is faster. However, the MERGE Destination stores the working data in memory, so it should be more efficient at loading the data. This does require the MERGE Destination to create a type and a stored procedure in the destination database, so the appropriate permissions need to be provided. The Batch Destination uses the ADO.NET Bulk Copy functionality, so the data is persisted in a working or temporary table in the target database, and cleaned up afterward, which is likely to be slower. However, you don’t need any special permissions to create a temporary table.

This isn’t intended to be a “this one is better than that one”. I can see using either or both as circumstances dictate. I just think it’s nice to have options.

SSIS Tasks and Components

Todd McDermid (a frequent poster on the MSDN SSIS forums, and creator of the Kimball SCD component) and I have started a new project on CodePlex along with Todd McDermid, the SSIS Community Tasks and Components (ssisCTC) project. This project is intended to be a umbrella project for custom SSIS tasks and components that are released as open source, as well as provide a listing of other open components for SSIS. I’ve published the Batch Destination component that I created to the site, and intend to add some more over the coming months.

Why set up a community project around this? Primarily, because I’ve found that many single person projects get one release, and then are never touched again. Having multiple people involved helps insure that if one person gets busy, or moves on to other interests, there are still people around to maintain and enhance what’s been put out there. So, if you are interested in joining or contributing, please let me know, or get in touch with use through the CodePlex site. Also, if you have or know of a freely available SSIS component or task, let us know, and we’ll be happy to add it to the list.

Running Password Protected Packages

A recent post on the forums was asking about running packages programmatically. The poster’s package was password protected, and when running the package, they were receiving a message that one of the connection managers was failing to connect.

The code being used was similar to this:

        Dim app As Microsoft.SqlServer.Dts.Runtime.Application
Dim pkg As Microsoft.SqlServer.Dts.Runtime.Package

app = New Microsoft.SqlServer.Dts.Runtime.Application()
pkg = app.LoadPackage(“C:\Projects\TestPackage.dtsx”, Nothing)
pkg.PackagePassword = “password”
pkg.Execute()


This code compiles fine, but does have a small error. The password is being set on the Package object, after it has been loaded. In this case, the Package’s ProtectionLevel property was set to EncryptSensitiveWithPassword, so the error didn’t show up until the package was executed. The problem is that the password needs to be set on the Application object, before the package is loaded. This makes sense, since encrypted values are decrypted when the package is loaded. The following code works fine:

        Dim app As Microsoft.SqlServer.Dts.Runtime.Application
Dim pkg As Microsoft.SqlServer.Dts.Runtime.Package

app = New Microsoft.SqlServer.Dts.Runtime.Application()
app.PackagePassword = “password”
pkg = app.LoadPackage(“C:\Projects\TestPackage.dtsx”, Nothing)
pkg.Execute()


This particular problem is a lot easier to see if the Package ProtectionLevel property is EncryptAllWithPassword, because you’ll get an error as soon as you load the package.

I’m not positive of this (the documentation is a little lacking), but I’m guessing you’d set the Password property on the Package object to encrypt a package that you had created programmatically. Evidently, setting it will not decrypt any existing encrypted values.

Scanning a Package For Specific Tasks

I recently had a need to scan through an SSIS package to find all instances of data flow tasks within the package. This is to support a future enhancement in BIDSHelper. However, I ended up making the code a little more general, so it can be used to get a list of all instances of any type of tasks within a package.


Before looking at the code, there is a concept that it is helpful to understand. SSIS packages have a nested structure. Everything is held within a container. Some containers, like the Sequence or ForEach Loop containers, can hold groups of other containers. The package itself is a container. All containers that can hold multiple containers implement the IDTSSequence interface. The TaskHost container, however, holds a single task, and does not implement the IDTSSequence interface. The TaskHost is what we have to find to identify individual instances of tasks.


The function below is designed to be called recursively. It uses the IDTSSequence interface to iterate through the Executables collection, which can hold either TaskHosts or containers that implement IDTSSequence themselves, which is where the recursion comes in. It also takes the type name as a string parameter. The code checks the type name string against the name of the type as returned by the GetType function. It’s simply checking to see if the typeName string exists within the full string returned, so it doesn’t have to be an complete match. This means you don’t have to specify the full namespace, like “Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask”. Instead, you can just specify “ExecuteSQL”.

        List<DtsContainer> FindExecutablesByType(IDTSSequence sequence, string typeName)
{
string typeNameUpper = typeName.ToUpper();
List<DtsContainer> matchingExecutable = new List<DtsContainer>();
foreach (Executable e in sequence.Executables)
{
if (e.GetType().ToString().ToUpper().Contains(typeNameUpper))
{
matchingExecutable.Add((DtsContainer)e);
}
if (e is TaskHost)
{
TaskHost taskHost = (TaskHost)e;

if ((typeNameUpper.Contains(“DATA FLOW”)
|| typeNameUpper.Contains(“DATAFLOW”)
|| typeNameUpper.Contains(“MAINPIPE”)
|| typeNameUpper.Contains(“PIPELINE”))
&& taskHost.InnerObject is IDTSPipeline90)
{
matchingExecutable.Add((DtsContainer)e);
}
else if (taskHost.InnerObject.GetType().ToString().ToUpper().Contains(typeNameUpper))
{
matchingExecutable.Add((DtsContainer)e);
}
}
if (e is IDTSSequence)
{
matchingExecutable.AddRange(FindExecutablesByType((IDTSSequence)e, typeNameUpper));
}

}
return matchingExecutable;
}


The function returns a list of DtsContainer objects, which is a base class for all control flow objects. The DtsContainer gives access to the name and description for each object. There is some special handling for Data Flow objects. These are actually implemented as COM objects, so a call to GetType on them returns “ComObject” rather than the .NET class name. To work around this, I have hard-coded a few values for the data flow objects, and a specific check for the IDTSPipeline object. There may be a better way to accomplish this, but I haven’t located it yet. Another limitation of this code is that it does not check event handlers. Any tasks inside of event handlers will not be found. This could be added fairly easily, but wasn’t necessary for my current purposes, so it didn’t make the cut.


To call this function and iterate the entire package, you can use this code:

Microsoft.SqlServer.Dts.Runtime.Application ssisApp = new Microsoft.SqlServer.Dts.Runtime.Application();
Package pkg = ssisApp.LoadPackage(“C:\\Temp\\MyPackage.dtsx”, null);
List<DtsContainer> containers = FindExecutablesByType((IDTSSequence)pkg, “ExecuteSQL”);
foreach (DtsContainer exec in containers)
{
//Do Something
}

I’ve put together a small GUI around this, posted on my SkyDrive.



If you are interested in learning more about working with SSIS programmatically, please post in the comments. I’m considering doing a few more posts on this, but only if there is interest.