Programming and

Extending SSIS

Out of the box, Microsoft provides a huge list of components for you in SSIS. When you find

that none of them fits your job, you need to be able to create your own. Initially this can be a

steep learning curve, but hopefully with the help of this chapter you will be able to overcome

this. In this chapter you will focus on the pipeline— not because it is better than any other area of

programmability within SSIS but because it will probably be the area where you have the most

benefit to gain, and it does require a slightly greater level of understanding. It also allows you to

see some of the really interesting things that Microsoft has done in SSIS. All forms of extensibility

Most people think that even the most timid or “code scared” DBAs, when faced with not having

what they want in the SSIS box, will be able to do a very reasonable job of designing and building

the component they need themselves. Because SSIS is now hosted in the Visual Studio shell, tradi-

tional programmers may be most at home here, but that’s no reason for everyone else to be left out

in the cold. Hopefully by the end of this chapter you’ll see that it doesn’t have to be that way.

The Sample Components

Three sample components will be defined in this section to demonstrate the main component

types. The transform component will then be expanded in Chapter 15 to include a user interface.

All code samples will be available on the Web site for this book, which you can find at

www.wrox.com.

❑ Accept a Connection Manager

❑ Validate the Connection Manager (did it get the right type of Connection Manager?)

❑ Add output columns to the component for the downstream processes

❑ Connect to the data source

❑ Get the data from the data source

❑ Assign the correct parts of the data to the correct output columns

❑ Handle any data errors

This component is going to need to do quite a bit of work in order to present its data to the outside

world. Stick with it and you’ll see how easy this can be. Your aim in the source adapter is to be able to

take a file with a custom format, read it, and present it to the downstream components. The file will look

Component 2: Transformation

The transform is where you are going to take data from a source, manipulate it, and then present the

newly arranged data to the downstream components. This component performs the following tasks:

❑ Create input columns to accept the data from upstream

❑ Validate the data to see that it is how the component expects it

❑ Check the column properties because this transform will be changing them in place

❑ Handle somebody trying to change the metadata of the transform by adding or removing

The destination adapter is basically a reverse of the source adapter. When it receives the input rows, it

needs to create a new file with data resembling that of the source file except that some of the data will be

the opposite way around compared to when it started out in the pipeline.

The components you’ll build are really quite simple, but the point is not their complexity, but how you

use the methods in Microsoft’s object model.

which are primarily design-time, others runtime. If you are using native code, then the divide between

the runtime and design-time is clearer because they are implemented on different interfaces. Commentary

on the methods has been divided into these two sections, but there are some exceptions, notably the

connection-related methods; a section on Connection Time–related methods is included later on.

Design-Time

and you could quite rightly think that there is little point in ever overriding them, as there isn’t much to

add to the base implementation. As mentioned, these are your verification methods, and code has been

sections expand on the types of checks you can do, and if you want to build a robust component, these

are well worth looking into.

is left to

puts to be manipulated anyway. The sorts of procedures you may want to set in here are listed below.

❑ Remove existing settings, such as inputs and outputs. This allows the component to be rebuilt

❑ Add inputs and outputs, ready for column work later on in the component lifetime. You may

❑ Define the connection requirements. By adding an item to the RuntimeConnectionCollection,

runtime, but the most interesting work is usually the result of a design-time call. As the name suggests,

it validates that the content of the component is correct and will enable you to at least run the package. If

the validation encounters a problem, then the return code used is important to determine any further

actions, such as calling

ReinitializeMetadata. The base class version of Validate performs its own

checks in the component, and you will need to extend it further in order to cover your specific needs.

Validate should not be used to change the component at all; it should only report the problems it finds.

The ReinitializeMetaData method is where all the building work for your component is done. You

add new columns, remove invalid columns, and generally build up the columns. It is called when the

columns even when the data source is not available. It is not required, but it makes the user experience

better. If the component declares that it will be using External Metadata (IDTSComponentMetaData90

.ValidateExternalMetadata), then the user in the advanced UI will see upstream columns to the left and

the external columns on the right; if you are validating your component against an output, you will see

the checked listbox of columns.

There are several methods you can use to deal with inputs and outputs. The three functions you may

need to perform are adding, deleting, and setting a custom property. The method names clearly indicate

their functions:

is that you will be removing from the InputColumnCollection this InputColumn. This differs

from the other two usage types, which add a reference to the InputColumn to the

InputColumnCollection if it does not exist already or you may be changing its Read/Write

property.

more information, and generally preparing by gathering all the information you require in advance. This

is stored in a variable, making it faster to access multiple times rather than creating objects during the real

execution for every row. This is the earliest point in the component that you will access the component’s

Buffer Manager, so you have the live context of columns, as opposed to the design-time representation.

The live and design time representations of columns may not match. The design time may contain more

ProcessInput method is never called, and all of the work is done through

PrimeOutput. In a destination adapter, it is the opposite way around. The PrimeOutput method is

never called, and the whole of the work is done through the

ProcessInput method.

Things are not quite that simple with a transform. There are two types of transforms, and the type of

transform you are writing will dictate which method or indeed methods your component should call.

PrimeOutput is not called and therefore all the work is done in the

❑ Asynchronous: Both methods are called here. The only difference really between a synchronous

you’re going to wonder about the difference between that and this method. The answer is, for this

release, nothing. If you want to think about this logically, then PreExecute is married to

PrepareForExecute.

As the method name suggests, this is called as the very last thing your component will do, and it is your

chance to clean up whatever resources may be left. However, it is rarely used, like

PostExecute.

it is expected to return a full error description string for the code specified. These two values are then

included in the columns of the error output.

Connection Time

These two methods are called several times throughout the life cycle of a component, both at design-

time and at runtime, and are used to manage connections within the component.

should be closed and released. If the connection was cached in a member variable, use that reference to

issue any appropriate

Close or Dispose methods. For some connections, such as a File Connection

Manager, this may not be relevant as all that was returned was a file path string, but if you took this a

stage further and opened a text stream or similar on the file, it should now be closed.

Building the Components

the most commonly used methods when building your own components. They also help give you an idea

of what the composition of a component resembles, the order in which things happen, and which method

is meant to do what. They will not implement all the available methods. The components have been built

in the GAC, and how to sign the assembly. This is common to all three components, so it will be dealt

with as one also.

These three lines of code simply help to identify your component when you look in the property pages

after adding it to the designer. The only property here that may not be obvious is

fil = (Microsoft.SqlServer.Dts.Runtime.DTSFileConnectionUsageType)

if (_fil != DTSFileConnectionUsageType.FileExists)

{

throw new Exception(“The type of FILE connection manager must be an

Existing File”);

_filename = ComponentMetaData.RuntimeConnectionCollection[“File To

}

}

}

This method covers a lot of ground and is really quite interesting. The first thing you want to do is find

out if you can get a Connection Manager from the runtime connection collection of the component. The

runtime connection was defined during

user has not provided a runtime connection.

if (ComponentMetaData.RuntimeConnectionCollection[“File To Read”].ConnectionManager

Once you have the managed Connection Manager, you can start to look at some of its properties and

make sure that it is what you want. All Connection Managers have a

component, you want to make sure that the

if (cm.CreationName != “FILE”)

If the Creation Name is not FILE, then you send an exception back to the component.

throw new Exception(“The type of FILE connection manager must be an Existing

You’ve established that a connection has been specified and that it is the right type. The problem with

raise an error if this is not the case.

DTSFileConnectionUsageType.CreateFolder — The folder does not yet exist and will be

created by the component. If the folder does exist, then you can decide how to handle this situa-

tion as with

CreateFile above.

You’re nearly done checking your Connection Manager now. At this point, you need the file name so

you can retrieve the file later on when you need to read it for data. You do that like this:

That concludes the AcquireConnections method, so you can now move straight on to the Validate

method.

ComponentMetaData.FireError(0, ComponentMetaData.Name, “Unexpected input

return DTSValidationStatus.VS_ISCORRUPT;

}

if (ComponentMetaData.RuntimeConnectionCollection[“File To

{

ComponentMetaData.FireError(0, “Validate”, “No Connection Manager

return DTSValidationStatus.VS_ISBROKEN;

// columns? Maybe somebody removed them through code.

if (DoesEachOutputColumnHaveAMetaDataColumnAndDoDatatypesMatch(output.ID) ==

{

ComponentMetaData.FireError(0, “Validate”, “Output columns and metadata

columns are out of sync. Making call to ReinitializeMetaData.”, “”, 0, out

return DTSValidationStatus.VS_NEEDSNEWMETADATA;

}

return base.Validate();

The next thing you do is check that the user has specified a Connection Manager for your component. If

not, then you return back to the user a message indicating that a Connection Manager is required. Again,

you do this through the

FireError method. If there is no Connection Manager specified, then you tell

the component it is broken. Remember that you do the validation of any Connection Manager that is

specified in

AcquireConnections().

if (ComponentMetaData.RuntimeConnectionCollection[“File To Read”].ConnectionManager

{

Validate() method will return

DTSValidationStatus.VS_NEEDSNEWMETADATA, which in turn calls ReinitializeMetaData. You

will see later what happens in that method.

if (ComponentMetaData.OutputCollection[“Component

Output”].OutputColumnCollection.Count == 0)

{

ComponentMetaData.FireError(0, “Validate”, “No output columns specified. Making

call to ReinitializeMetaData.”, “”, 0, out pbCancel);

return DTSValidationStatus.VS_NEEDSNEWMETADATA;

}

So if the output has output columns, then one of the things you want to check for is whether the

output columns have an ExternalMetaDataColumn associated with them. You’ll recall that in

Once you have that, the code loops over the output columns in that output and asks if the

This is a really simple way of doing things. Basically, you are going to remove all the

ExternalMetaDataColumns and then remove the output columns. You will then add them back using the

about the data type.

IDTSOutputColumn90 outName = output.OutputColumnCollection.New();

outName.Name = “Name”;

outName.Description = “The Name value retieved from File”;

outName.SetDataTypeProperties(DataType.DT_STR, 50, 0, 0, 1252);

You now look to create an ExternalMetaDataColumn for the OutputColumn, and you do that by call-

ing the method

ExternalMetaDataColumnCollection of the output and the Column for which you want to create an

ExternalMetaDataColumn.

ExternalMetadataColumnID property of the output column.

IDTSExternalMetadataColumn90 eColumn = externalCollection.New();

eColumn.Name = column.Name;

eColumn.DataType = column.DataType;

eColumn.Precision = column.Precision;

eColumn.Length = column.Length;

eColumn.Scale = column.Scale;

eColumn.CodePage = column.CodePage;

column.ExternalMetadataColumnID = eColumn.ID;

columns and enter them into a struct so you could easily retrieve them later. You’re not going to do that

here, but you do this in the destination adapter, so you could port what you do there into this adapter as

well. The only method you are interested in with this adapter is

PrimeOutput. Here is the method in

full:

public override void PrimeOutput(int outputs, int[] outputIDs, PipelineBuffer[]

{

ParseTheFileAndAddToBuffer(_filename, buffers[0]);

buffers[0].SetEndOfRowset();

tion is zero-based. You’ll look at the ParseTheFileAndAddToBuffer method in a moment, but the last

thing you do in this method is call

TextReader tr = File.OpenText(filename);

IDTSOutput90 output = ComponentMetaData.OutputCollection[“Component Output”];

IDTSOutputColumnCollection90 cols = output.OutputColumnCollection;

IDTSOutputColumn90 col;

string s = tr.ReadLine();

while (s != null)

{

if (s.StartsWith(“<START>”))

buffer.AddRow();

}

if (s.StartsWith(“Age:”))

{

col = cols[“Age”];

i = BufferManager.FindColumnByLineageID(output.Buffer, col.LineageID);

Int32 value;

if (s.Substring(4).Trim() == “”)

value = Convert.ToInt32(s.Substring(4).Trim());

i = BufferManager.FindColumnByLineageID(output.Buffer, col.LineageID);

if (s.Substring(8).Trim() == “”)

value = true;

value = s.Substring(8).Trim() != “1” ? false : true;

buffer.SetBoolean(i, value);

}

if (s.StartsWith(“Salary:”))

You do the same when the user tries to add an input to your component in the InsertInput method:

[CLSCompliant(false)]

public override IDTSInput90 InsertInput(DTSInsertPlacement insertPlacement, int

{

throw new Exception(“You cannot insert an output (“ +

outputID.ToString() + “)”);

}

Notice again how in both methods you throw an exception in order to tell the user that what they

requested is not allowed.

If the component were asynchronous, you would need to add columns to the output yourself. There

Instead of users trying to add an output or an input to your component, they are trying to remove one of

them. You do not want to allow this either, so you can use the

methods to tell them. Here are the methods as implemented in your component.

First the

DeleteInput Method:

[CLSCompliant(false)]

public override void DeleteInput(int inputID)

{

throw new Exception(“You cannot delete an input”);

}

That concludes the code for the design-time part of your transformation component. Now you will

move on to the runtime methods.

The first runtime method you’ll be using is the

once in your component’s life, and it is where you typically do most of your setup using the struct men-

tioned at the top of this section. It is the first opportunity you get to access the Buffer Manager, provid-

ing access to columns within the buffer, which you will need in

ProcessInput as well. Keep in mind

that you will not be getting a call to

{

IDTSInputColumn90 column = input.InputColumnCollection[x];

_inputColumnInfos[x] = new ColumnInfo();

_inputColumnInfos[x].bufferColumnIndex =

_inputColumnInfos[x].columnDisposition = column.ErrorRowDisposition;

_inputColumnInfos[x].lineageID = column.LineageID;

}

The first thing this method does is get a reference to the input collection. The collection is zero-based,

and because you have only one input, you have used the indexer and not the name, though you could

have used the name as well.

At the start of this section was a list of the things your component would need later. This included a

struct that you were told you would use in various guises, and it also included an array of these structs.

You now need to size the array, which you do next by setting the size of the array to the count of

You then read from the column the details you require and store them in the ColumnInfo object. The

first thing you want to retrieve is the location of the column in the buffer. You cannot simply do this by

the order that you added them to the buffer. Though this would probably work, it is likely to catch you

out at some point. You can find the column in the buffer by the use of a method called

of the column that you wish to find as arguments.

You now need only two more details about the input column: the LineageID and the

ErrorRowDisposition. Remember, ErrorRowDisposition tells the component how to treat an error.

When you start to build your own components, you will see that this method really becomes useful. You

can use it to initialize any counters you may need or to open connections to data sources as well as any-

thing else you think of.

ProcessInput. Remember, this is

ProvideComponentProperties, and this is the method in

which the data is moved and manipulated. This method contains a lot of information that will help you

int errorOutputID = -1;

int errorOutputIndex = -1;

IDTSInput90 inp = ComponentMetaData.InputCollection.GetObjectByID(inputID);

#region Output IDs

GetErrorOutputInfo(ref errorOutputID, ref errorOutputIndex);

// There is an error output defined

errorOutputID = ComponentMetaData.OutputCollection[“RSErrors”].ID;

GoodOutputId = ComponentMetaData.OutputCollection[“ReverseStringOutput”].ID;

if (!buffer.EndOfRowset)

The method GetErrorOutput returns the output ID and the Index of the error output. Remember that

you defined the error output in

ErrorRowDisposition property of the input in ProvideComponentProperties,

{

case DTSRowDisposition.RD_RedirectRow:

buffer.DirectErrorRow(errorOutputID, 0, buffer.CurrentRow);

case DTSRowDisposition.RD_FailComponent:

throw new Exception(“There was an error in your processing “ +

case DTSRowDisposition.RD_IgnoreFailure:

buffer.DirectRow(GoodOutputId);

Now how could this have looked had it been an asynchronous transform? You would get a buffer from

both

PrimeOutput and ProcessInput. ProcessInput would contain the data and structure that came

into the component, and

on. The trick here is to get the data from one buffer into the other. Here is one way you can approach it.

At the class level, create a variable of type

You now have a cached version of the buffer from PrimeOutput, and you go straight over to

The requirement for the destination adapter is that it accepts an input from an upstream component of

any description and converts it to a format similar to that seen in the source adapter. The component will

accept a

cant amount of validation. You also need to validate whether the component is structurally correct, and

if it isn’t, you need to correct things. The first thing you always need to do is declare some variables that

will be used throughout the component. You also need to create the very valuable struct that is going to

store the details of the columns, which will be needed in

#region Variables

You should quickly run through the meaning of these variables and when they will be needed. The

Manager the user has assigned to your component.

#region ColumnInfo

private struct ColumnInfo

{

public int BufferColumnIndex;

public string ColumnName;

}

The struct will be used to store the index number of the column in the buffer and also to store the name

{

ComponentMetaData.RuntimeConnectionCollection.RemoveAll();

RemoveAllInputsOutputsAndCustomProperties();

ComponentMetaData.Name = “Professional SSIS Destination Adapter”;

ComponentMetaData.Description = “Our first Destination Adapter”;

ComponentMetaData.ContactInfo = “www.Konesans.com”;

IDTSRuntimeConnection90 rtc =

rtc.Name = “File To Write”;

rtc.Description = “This is the file to which we want to write”;

IDTSInput90 input = ComponentMetaData.InputCollection.New();

The first part of the method gets rid of any runtime Connection Managers that the component may

have and removes any custom properties, inputs, and outputs that the component has. This makes the

component a clean slate to which you can now add back anything it may need.

This piece of code gives the user the opportunity to specify a Connection Manager for the component.

This will be the file to which you write the data from upstream.

The next thing you do is add back the input. This is what the upstream component will connect to, and

through which you will receive the data from the previous component. Now you need to make sure that

the IDTSInput90 object of the component remains in the execution plan regardless of whether it is

attached by making the

tasks this method accomplishes are the following:

FILE Connection Manager

❑ Make sure that the FILE Connection Manager has a FileUsageType property value of

❑ Get the file name from the Connection Manager

public override void AcquireConnections(object transaction)

{

bool pbCancel = false;

if (ComponentMetaData.RuntimeConnectionCollection[“File To

Write”].ConnectionManager != null)

{

Manager is not a FILE Connection Manager”, “”, 0, out pbCancel);

}

{

(DTSFileConnectionUsageType)cm.Properties[“FileUsageType”].GetValue(cm);

_filename = ComponentMetaData.RuntimeConnectionCollection[“File To

Read”].ConnectionManager.AcquireConnection(transaction).ToString();

if (_filename == null || _filename.Length == 0)

{

returned when grabbing the filename”, “”, 0, out pbCancel);

}

input to the component is correct, and if it isn’t, you’ll try to fix what is wrong by calling

ReinitializeMetaData. Here is the Validate method:

{

ComponentMetaData.FireError(0, ComponentMetaData.Name, “Unexpected Output

return DTSValidationStatus.VS_ISCORRUPT;

}

if (ComponentMetaData.RuntimeConnectionCollection[“File To

{

ComponentMetaData.FireError(0, “Validate”, “No Connection Manager returned”,

return DTSValidationStatus.VS_ISCORRUPT;

}

if (ComponentMetaData.AreInputColumnsValid == false)

{

You now want to check to make sure the user specified a Connection Manager. Remember that you are

ComponentMetaData.FireError(0, “Validate”, “No Connection Manager returned”, “”,

return DTSValidationStatus.VS_ISCORRUPT;

You then need to add a breakpoint to the Data Flow task that is hit when the Data Flow task hits the

You are now ready to execute your package. Hit F5 and allow the breakpoint in the Data Flow task to be

hit. When you hit the breakpoint, switch back to the component’s design process and follow the steps

detailed earlier when debugging the design-time in order to get to the screen where you chose what pro-

cess to debug.

When you execute a package in the designer, it is not really the designer that is doing the work. It hands

off the execution to a process called DtsDebugHost.exe. This is the package that you want to attach to, as

shown in Figure 14-11.

Figure 14-11

Click Attach and watch the debug symbols being loaded by the project. Before returning to the SSIS

package, you need to set a breakpoint on one of the runtime methods used by your component, such as

That concludes your look at the first method for debugging the runtime. The second method involves

BIDS indirectly because you need to create a package like this one that you can call later. After that, you

do not need BIDS at all. You do, however, still need the component’s design project open. Open your

project’s properties and look at the Debug tab on the left, which should look similar to Figure 14-13.

As you can see, you have said that you want to start an external program to debug. That program is

DTExec, which is the new and more powerful version of DTSRun. On the command line, you will pass a

parameter /FILE to DTExec. This tells DTExec the name and location of the package you just built. Make

sure you still have a breakpoint set on PreExecute, and hit F5in your project. A DOS window will appear

and you will see some messages fly past, which are the same messages you would see in the designer.

Eventually you will get to your breakpoint, and it will break in exactly the same way as it did when you

were using BIDS. So, why might you use one over the other? The most obvious answer is speed. It is

much faster to get to where you want to debug your component using DTExec than it is doing the same

in BIDS. The other advantage is that you do not need to have two tools open at the same time. You can

The components you have designed are certainly very simple in nature, but hopefully this chapter will

give you the confidence to experiment with some of your own unique requirements.