SSIS provides APIs to programmatically create dtsx packages. While we provide a managed wrapper layer for the runtime/control flow, you have to use the lower level COM wrappers (DTSPipelineWrap) to create your data flows – the usability of which could definitely be improved.
Evgeny Koblov, one of the testers on the SSIS team, has put together a really powerful framework for generating packages called EzAPI . It’s been used by the test team internally for awhile now, and we’ve now published it to Codeplex as well. The project includes a pretty detailed readme file, as well as some great samples on how to use the classes.
This post is mostly a formatted version of the readme file written by Evgeny. It is available in the root directory of the project’s source code.
Overview
This sample provides some functionality to easily create SSIS packages programmatically and dynamically alter their objects (tasks, components, changing metadata, connection strings, etc). This framework supports:
- Creation SSIS packages of any complexity including both SSIS runtime and pipeline (tasks, containers and components)
- BIDS like behavior (automatic column mapping in destinations, automatic metadata refresh, default values of properties, etc)
Requirements
To use EzAPI framework and be able to compile and run the demo applications, the following components are required:
- The sample must be installed on the local hard drive
- You must install the common tools for SQL Server 2008
- You must install SQL Server Integration Services 2008
- Adventure Works sample database installed under a default instance on the local machine
Installer
The installer will place source and project files into the chosen directory. Additionally, pre-built binaries are placed into the Global Assembly Cache (GAC) and into the C:\Program Files\Microsoft SQL Server\100\DTS\Binn folder
Background
Having an easy way to create SSIS packages programmatically is vital. In some cases static packages do not provide enough flexibility – it is hard to change property task or component of a static package using SSIS object model while using configurations is not always possible. Current approach to create SSIS package programmatically is to use SSIS object model directly. However SSIS object SSIS object model was designed to be universal for all kinds of components (native and managed ones) and to separate runtime and design time phases of component usage. However both of the stages use the same metadata. This is the reason why SSIS distinguishes design-time, runtime functionality and metadata. The side effect is that more code is necessary to create a package and once the package is created – it is not very convenient to change properties of pipeline components and especially metadata of input, output, virtual and external columns.
However, in .NET managed world we can use all the advantages of OOP and have a corresponding class that will encapsulate both metadata and operations that can be made on them – so, there is no need to separate it as we do not deal with runtime internals during design phase.
The framework proposed in this sample can significantly reduce the amount of code that need to be supported and allows more efficient development of SSIS packages programmatically.
EzAPI Architecture
EzAPI takes SSIS COM object (implemented via many C++ and C# classes) as a single entity. It means it maps each EzAPI object to each SSIS COM object. Here is how it approximately looks like for control flow and for data flow:
In the control flow – the base entity is executable, that can be either a container or a task. So EzContainer and EzTask classes derive from EzExecutable class. Then all the containers derive from EzContainer and all tasks derive from EzTask.
In the pipeline – component is the base entity. We have three types of components (can be easily noticed in BIDS): Sources, Destinations and Transformations. Sources and Destinations are actually adapters that connect SSIS with some input or output (Database, Flat File, Variable, etc) That’s why they share some functionality and derive from EzAdapter class.
This approach allows accumulating common functionality in the base classes on each level of hierarchy. When you use SSIS object model or PackageBuilder you should constantly duplicate code that retrieves instances of SSIS objects, their metadata, implements column mapping logic (EzAPI allows both – default , BIDS-like mapping, and custom mapping via special functions MapColumn and UnmapColumn), etc.
Using EzAPI
This section contains some examples of how EzAPI can be used
To design a package using EzAPI:
- Create a new .NET project.
- Add reference to EzAPI.dll.
- Add using entry: using Microsoft.SqlServer.SSIS.EzAPI;
Creation of package with single task
The example below creates packages with ExecutePackageTask:
using Microsoft.SqlServer.SSIS.EzAPI;…
namespace ConsoleApplication1
{
// DEMO1: This package contains a single Execute package task
public class EzExecPkgPackage : EzPackage
{
// Provide this constructor only if you want to overload Assignment operator
public EzExecPkgPackage(Package p) : base(p) { } // this constructor MUST BE present
// Provide assignment operator if you want to be able to Assign SSIS Package to EzPackage
public static implicit operator EzExecPkgPackage(Package p) { return new EzExecPkgPackage(p); }
// All the tasks, components and connection managers which should be linked to the corresponding
// SSIS package objects MUST BE PUBLIC MEMBERS if you want to be able to assign SSIS package to EzPackage
public EzExecPackage ExecPkg;
public EzFileCM PkgCM;
public EzExecPkgPackage(string pkgName)
: base()
{
PkgCM = new EzFileCM(this);
PkgCM.ConnectionString = pkgName;
PkgCM.Name = "PackageConnection";
ExecPkg = new EzExecPackage(this);
ExecPkg.Name = "ExecutePackage";
ExecPkg.Connection = PkgCM;
}
[STAThread]
static void Main(string[] args)
{
// DEMO 1
EzPackage p = new EzPackage();
p.SaveToFile("testpkg.dtsx");
EzExecPkgPackage p1 = new EzExecPkgPackage("testpkg.dtsx");
p1.SaveToFile("demo1.dtsx");
p1.Execute();
Console.Write(string.Format("Package1 executed with result {0}\n", p1.ExecutionResult));
}
}
}
This code will create package with a single ExecutePackageTask:
Creation of package with simple dataflow
The next example shows how to create a package with a simple dataflow that has OleDB source and FlatFile destination.
namespace ConsoleApplication1
{
// DEMO2: Simple data transfer from source to destination. Use EzSrcDestPackage template for this
public class EzOleDbToFilePackage : EzSrcDestPackage<EzOleDbSource, EzSqlOleDbCM, EzFlatFileDestination, EzFlatFileCM>
{
public EzOleDbToFilePackage(Package p) : base(p) { }
public static implicit operator EzOleDbToFilePackage(Package p) { return new EzOleDbToFilePackage(p); }
public EzOleDbToFilePackage(string srv, string db, string table, string file)
: base()
{
SrcConn.SetConnectionString(srv, db);
Source.Table = table;
DestConn.ConnectionString = file;
Dest.Overwrite = true;
// This method defines the columns in FlatFile connection manager which have the same
// datatypes as flat file destination
Dest.DefineColumnsInCM();
}
[STAThread]
static void Main(string[] args)
{
// DEMO 2
EzOleDbToFilePackage p2 = new EzOleDbToFilePackage("localhost", "AdventureWorks", "Address", "result.txt");
p2.DataFlow.Disable = true;
p2.Execute();
Console.Write(string.Format("Package2 executed with result {0}\n", p2.ExecutionResult));
}
}
}
Dataflow task of the created package looks like this:
Notice that the components are already attached, fields in connection managers are defined. Also Input and external columns with the same names are automatically mapped in destination:
Another thing that is worth mentioning is that in this example a template class was used:
EzSrcDestPackage<EzOleDbSource, EzSqlOleDbCM, EzFlatFileDestination, EzFlatFileCM>
Here is where EzAPI becomes really powerful. Even this simple template class allows creation of packages with dataflow task with any source and any destination, by just specifying template parameters. As for all the properties of package, dataflow task, or components – they can be set directly in the testcase, instead of passing them to “builder” using helper classes.
Package with DataFlow transformation
Now let’s create a package that has structure used by most test suites that create packages programmatically, i.e. package with dataflow: source->transform->destination:
class EzMyPackage<EzTransform> : EzTransformPackage<EzOleDbSource, EzSqlOleDbCM, EzTransform, EzFlatFileDestination, EzFlatFileCM> where EzTransform: EzComponent
{
// These two methods are not deployed as I am not going to assign SSIS package to EzSortPackage in demo
public EzMyPackage(Package p) : base(p) { }
public static implicit operator EzMyPackage<EzTransform>(Package p) { return new EzMyPackage<EzTransform>(p); }
public EzMyPackage(string srv, string db, string sql, string file) : base()
{
SrcConn.SetConnectionString(srv, db);
Source.SqlCommand = sql;
DestConn.ConnectionString = file;
Dest.Overwrite = true;
Dest.DefineColumnsInCM();
}
}
And now – this is how package with Sort Transform can be created and used:
public static void Main(string[] args)
{
// DEMO 3
EzMyPackage<EzSortTransform> p3 = new EzMyPackage<EzSortTransform>("localhost", "AdventureWorks", "select * from Person.Address", "result1.txt");
p3.Transform.EliminateDuplicates = true;
p3.Transform.SortOrder["AddressID"] = -1; // sort in descending order
p3.SaveToFile("demo3.dtsx");
p3.Execute();
Console.Write(string.Format("Package3 executed with result {0}\n", p3.ExecutionResult));
// Assign SSIS package to EzPackage
p3 = new Application().LoadPackage("demo3.dtsx", null);
p3.Execute();
Console.Write(string.Format("Package3 executed with result {0}\n", p3.ExecutionResult));
}
And this is how to turn it into package with DerivedColumn transform:
public static void Main(string[] args)
{
EzMyPackage<EzDerivedColumn> p = new EzMyPackage<EzDerivedColumn>("localhost", "AdventureWorks", "Address", "c:\\result1.txt");
p.Transform.Expression["SmallDate1"] = "DATEADD(\"Year\",1,SmallDate1)"; // Setting derived column expressions
p.Transform.Expression["Date1"] = "DATEADD(\"Year\",1,Date1)";
p.Transform.Expression["SmallDate2"] = "DATEADD(\"Quarter\",2,SmallDate2)";
p.Transform.Expression["Date2"] = "DATEADD(\"Quarter\",2,Date2)";
p.Transform.Expression["SmallDate3"] = "DATEADD(\"Month\",4,SmallDate3)";
p.Transform.Expression["Date3"] = "DATEADD(\"Month\",4,Date3)";
p.SaveToFile(@"C:\temp\ezdemo\demo3.dtsx");
p.Execute();
Console.Write(string.Format("Package3 executed with result {0}\n", p.ExecutionResult));
p = new Application().LoadPackage(@"C:\temp\ezdemo\demo3.dtsx", null); //assign SSIS package to EzPackage
p.Execute();
Console.Write(string.Format("Package3 executed with result {0}\n", p.ExecutionResult));
}
We didn’t have to create a separate class for that – we could use existing template without introducing any modifications.
Package with Loop containers
Another possible scenario is execution of some process in a loop. EzAPI provides this possibility and here is the example:
public class EzLoopSortPackage : EzLoopTransformPackage<EzOleDbSource, EzSqlOleDbCM, EzSortTransform, EzFlatFileDestination, EzFlatFileCM>
{
public EzLoopSortPackage(Package p) : base(p) { }
public static implicit operator EzLoopSortPackage(Package p) { return new EzLoopSortPackage(p); }
public EzLoopSortPackage(string srv, string db, string table, string file)
: base()
{
SrcConn.SetConnectionString(srv, db);
Source.Table = table;
Source.AccessMode = AccessMode.AM_OPENROWSET;
DestConn.ConnectionString = file;
Dest.Overwrite = true;
Dest.DefineColumnsInCM();
}
[STAThread]
static void Main(string[] args)
{
EzLoopSortPackage p5 = new EzLoopSortPackage("localhost", "AdventureWorks", "Address", "result1.txt");
p5.Transform.MaxThreads = -1; // Do not limit number of threads
p5.Transform.EliminateDuplicates = true;
p5.Transform.SortOrder["AddressID"] = -1; // sort in descending order
p5.Variables.Add("LoopCounter", false, "User", 0);
p5.ForLoop.InitExpression = "@[User::LoopCounter] = 0";
p5.ForLoop.AssignExpression = "@[User::LoopCounter] = @[User::LoopCounter] + 1";
p5.ForLoop.EvalExpression = "@[User::LoopCounter] < 3";
p5.Execute();
Console.Write(string.Format("Package5 executed with result {0}\n", p5.ExecutionResult));
}
}
This example creates a package with dataflow task executed in a for loop 3 times. The code above is pretty straightforward, all the property names are the same as they are in BIDS. Here is the package created:
Package with Multiple dataflows
Another important scenario is package with multiple dataflows. A typical example here is lookup transform with full or partial cache.
namespace ConsoleApplication1
{
public class EzLookupCachePkg : EzPkgWithExec<EzTransformDF<EzOleDbSource, EzSqlOleDbCM, EzCacheTransform>>
{
public EzTransDestConnDF<EzOleDbSource, EzSqlOleDbCM, EzLookup, EzFlatFileDestination, EzFlatFileCM> LookupDF;
public EzLookupCachePkg(string srv, string srcDb, string refDb, string refSql) : base()
{
LookupDF = new EzTransDestConnDF<EzOleDbSource, EzSqlOleDbCM, EzLookup, EzFlatFileDestination, EzFlatFileCM>(this);
LookupDF.AttachTo(Exec);
Exec.Transform.Connection = new EzCacheCM(this);
LookupDF.Transform.CacheConnection = Exec.Transform.Connection;
Exec.Transform.Connection.Name = "Cache";
Variables.Add("CheckSum0", false, "", new byte[] { });
Exec.Name = "CacheDF";
Exec.SrcConn.Name = "RefDb";
Exec.SrcConn.SetConnectionString(srv, refDb);
Exec.Source.SqlCommand = refSql;
Exec.Transform.ProvideInputToCache();
LookupDF.SrcConn.Name = "SrcDb";
LookupDF.SrcConn.SetConnectionString(srv, srcDb);
LookupDF.Transform.Meta.OutputCollection[0].ErrorRowDisposition = Microsoft.SqlServer.Dts.Pipeline.Wrapper.DTSRowDisposition.RD_IgnoreFailure;
LookupDF.Name = "LookupDF";
LookupDF.DestConn.ConnectionString = "demo2.txt";
}
public EzLookupCachePkg(Package p) : base(p) { }
public static implicit operator EzLookupCachePkg(Package p) { return new EzLookupCachePkg(p); }
// Field Names in this sample are not very good because we use templates a lot here
[STAThread]
static void Main(string[] args)
{
EzLookupCachePkg p5 = new EzLookupCachePkg(Environment.MachineName, "AdventureWorks", "AdventureWorks",
"select * from HumanResources.EmployeeAddress");
p5.Exec.Transform.Connection.SetIndexCols("EmployeeID", "AddressID");
p5.LookupDF.Source.SqlCommand = "select * from HumanResources.Employee";
p5.LookupDF.Transform.SetJoinCols("EmployeeID,EmployeeID");
p5.LookupDF.Transform.SetPureCopyCols("AddressID");
p5.LookupDF.Dest.DefineColumnsInCM();
p5.Exec.Disable = true;
p5.LookupDF.Transform.OleDbConnection = p5.Exec.SrcConn;
p5.LookupDF.Transform.SqlCommand = p5.Exec.Source.SqlCommand;
p5.SaveToFile("demo5.dtsx");
p5.Execute();
Console.Write(string.Format("Package6 executed with result {0}\n", p5.ExecutionResult));
}
private static string ArrayToString<T>(T[] arr)
{
if (arr == null)
return "null";
string res = string.Empty;
foreach (T el in arr)
res += el.ToString() + ",";
return res;
}
}
}
Now the question is: what if we want to reuse the package but don’t want cache transform to be built? The answer is – we can easily disable cache dataflow:
p6.Exec.Disable = true;
Right after that our package is ready. With case of PackageBuilder – we would have to create a separate builder without cache dataflow task.
Also pay attention to how easily lookup transform can be configured:
p6.LookupDF.Transform.SetJoinCols("EmployeeID,EmployeeKey", "LoginID,LoginID");
p6.LookupDF.Transform.SetPureCopyCols("EmployeeNationalIDAlternateKey", "ParentEmployeeNationalIDAlternateKey",
"FirstName", "LastName");
This results in the following picture in lookup transform:
Cross feature packages
Another great scenario is packages with multiple transforms in a more sophisticated dataflows. Here is an example of such package:
namespace ConsoleApplication1
{
// For EzAPI public fields means that if we assign SSIS Package to EzPackage this field needs
// to be assigned to the corresponding object inside SSIS package. The corresponding object is an object that has the same EzName as the field name
// in the class. If you set it to some incorrect value - package logic won't be affected as internally it stores all the Ez components, tasks and connections
// and uses that internal list to refresh metadata.
class EzMyPackage : EzDataFlowPackage
{
public EzOleDbSource Source;
// Transforms
public EzLookup Lookup;
public EzSortTransform SortMatch;
public EzSortTransform SortNoMatch;
// Destinations
public EzFlatFileDestination MatchDest;
public EzFlatFileDestination NoMatchDest;
public EzFlatFileDestination ErrorDest;
// Connection managers
public EzSqlOleDbCM RefConn;
public EzSqlOleDbCM SrcConn;
public EzFlatFileCM MatchCM;
public EzFlatFileCM NoMatchCM;
public EzFlatFileCM ErrorCM;
// Provide this constructor only if you want to overload Assignment operator
public EzMyPackage(Package p) : base(p) { }
// Provide assignment operator if you want to be able to Assign SSIS Package to EzPackage
public static implicit operator EzMyPackage(Package p) { return new EzMyPackage(p); }
public EzMyPackage() : base()
{
// Connection managers
SrcConn = new EzSqlOleDbCM(this);
SrcConn.SetConnectionString(Environment.MachineName, "AdventureWorks");
MatchCM = new EzFlatFileCM(this);
MatchCM.ConnectionString = "matchcm.txt";
NoMatchCM = new EzFlatFileCM(this);
NoMatchCM.ConnectionString = "nomatchcm.txt";
ErrorCM = new EzFlatFileCM(this);
ErrorCM.ConnectionString = "errorcm.txt";
RefConn = new EzSqlOleDbCM(this);
RefConn.SetConnectionString(Environment.MachineName, "AdventureWorks");
// Creating Dataflow
Source = new EzOleDbSource(DataFlow);
Source.Connection = SrcConn;
Source.SqlCommand = "select * from HumanResources.Employee";
Lookup = new EzLookup(DataFlow);
Lookup.AttachTo(Source);
Lookup.OleDbConnection = RefConn;
Lookup.SqlCommand = "select * from HumanResources.EmployeeAddress";
Lookup.SetJoinCols("EmployeeID,EmployeeID");
Lookup.SetPureCopyCols("AddressID");
Lookup.NoMatchBehavor = NoMatchBehavior.SendToNoMatchOutput;
Lookup.OutputCol("AddressID").TruncationRowDisposition = DTSRowDisposition.RD_RedirectRow;
SortMatch = new EzSortTransform(DataFlow);
SortMatch.AttachTo(Lookup, 0, 0);
SortMatch.SortOrder["EmployeeID"] = 1; // sort in ascending order
SortMatch.SortOrder["AddressID"] = -2; // sort in descending order
SortNoMatch = new EzSortTransform(DataFlow);
SortNoMatch.AttachTo(Lookup, 1, 0);
SortNoMatch.SortOrder["EmployeeID"] = 1; // sort in ascending order
ErrorDest = new EzFlatFileDestination(DataFlow);
ErrorDest.AttachTo(Lookup, 2, 0);
ErrorDest.Connection = ErrorCM;
ErrorDest.DefineColumnsInCM(); // configure connection manager to have all input columns defined in the resulting file
MatchDest = new EzFlatFileDestination(DataFlow);
MatchDest.AttachTo(SortMatch);
MatchDest.Connection = MatchCM;
MatchDest.DefineColumnsInCM();
NoMatchDest = new EzFlatFileDestination(DataFlow);
NoMatchDest.AttachTo(SortNoMatch);
NoMatchDest.Connection = NoMatchCM;
NoMatchDest.DefineColumnsInCM();
}
[STAThread]
static void Main(string[] args)
{
EzMyPackage p = new EzMyPackage();
p.Execute();
Console.Write(string.Format("Package executed with result {0}\n", p.ExecutionResult));
}
}
}
The result is:
As you can see, this relatively complex package is created even without using template possibilities of EzAPI and it the code that creates package (class constructor) is only 50 lines of code. So it took 4 lines per object to create the package. The dtsx file of this package contains more than 1300 lines.
Misc scenarios
Say we want the dataflow in a previous package to be executed in a loop, but we want a number of those loops concatenated together. Something like this:
// This demo creates a package with N forloops with dataflow inside executed sequentially
namespace ConsoleApplication1
{
class EzMyDataFlow : EzDataFlow
{
public EzOleDbSource Source;
// Transforms
public EzLookup Lookup;
public EzSortTransform SortMatch;
public EzSortTransform SortNoMatch;
// Destinations
public EzFlatFileDestination MatchDest;
public EzFlatFileDestination NoMatchDest;
public EzFlatFileDestination ErrorDest;
// Connection managers
public EzSqlOleDbCM RefConn;
public EzSqlOleDbCM SrcConn;
public EzFlatFileCM MatchCM;
public EzFlatFileCM NoMatchCM;
public EzFlatFileCM ErrorCM;
public EzMyDataFlow(EzContainer parent, TaskHost pipe) : base(parent, pipe) { }
public EzMyDataFlow(EzContainer parent) : base(parent)
{
// Connection managers
SrcConn = new EzSqlOleDbCM(Package, "SrcConn");
SrcConn.SetConnectionString(Environment.MachineName, "AdventureWorks");
MatchCM = new EzFlatFileCM(Package, "MatchCM");
MatchCM.ConnectionString = "matchcm.txt";
NoMatchCM = new EzFlatFileCM(Package, "NoMatchCM");
NoMatchCM.ConnectionString = "nomatchcm.txt";
ErrorCM = new EzFlatFileCM(Package, "ErrorCM");
ErrorCM.ConnectionString = "errorcm.txt";
RefConn = new EzSqlOleDbCM(Package, "RefConn");
RefConn.SetConnectionString(Environment.MachineName, "AdventureWorks");
// Creating Dataflow
Source = new EzOleDbSource(this);
Source.Connection = SrcConn;
Source.SqlCommand = "select * from HumanResources.Employee";
Lookup = new EzLookup(this);
Lookup.AttachTo(Source);
Lookup.OleDbConnection = RefConn;
Lookup.SqlCommand = "select * from HumanResources.EmployeeAddress";
Lookup.SetJoinCols("EmployeeID,EmployeeID");
Lookup.SetPureCopyCols("AddressID");
Lookup.NoMatchBehavor = NoMatchBehavior.SendToNoMatchOutput;
Lookup.OutputCol("AddressID").TruncationRowDisposition = DTSRowDisposition.RD_RedirectRow;
SortMatch = new EzSortTransform(this);
SortMatch.AttachTo(Lookup, 0, 0);
SortMatch.SortOrder["EmployeeID"] = 1; // sort in ascending order
SortMatch.SortOrder["AddressID"] = -2; // sort in descending order
SortNoMatch = new EzSortTransform(this);
SortNoMatch.AttachTo(Lookup, 1, 0);
SortNoMatch.SortOrder["EmployeeID"] = 1; // sort in ascending order
ErrorDest = new EzFlatFileDestination(this);
ErrorDest.AttachTo(Lookup, 2, 0);
ErrorDest.Connection = ErrorCM;
ErrorDest.DefineColumnsInCM(); // configure connection manager to have all input columns defined in the resulting file
MatchDest = new EzFlatFileDestination(this);
MatchDest.AttachTo(SortMatch);
MatchDest.Connection = MatchCM;
MatchDest.DefineColumnsInCM();
NoMatchDest = new EzFlatFileDestination(this);
NoMatchDest.AttachTo(SortNoMatch);
NoMatchDest.Connection = NoMatchCM;
NoMatchDest.DefineColumnsInCM();
}
}
public class EzMyLoopPkg : EzPackage
{
EzExecForLoop<EzMyDataFlow>[] Loops;
public EzMyLoopPkg(int numLoops) : base()
{
Variables.Add("i", false, "", 0);
Loops = new EzExecForLoop<EzMyDataFlow>[numLoops];
for (int i = 0; i < numLoops; i++)
{
Loops[i] = new EzExecForLoop<EzMyDataFlow>(this);
Loops[i].InitExpression = "@i=0";
Loops[i].EvalExpression = "@i<10";
Loops[i].AssignExpression = "@i=@i+1";
}
for (int i = 1; i < numLoops; i++)
Loops[i].AttachTo(Loops[i-1]);
}
public static void Main(string[] args)
{
EzMyLoopPkg p = new EzMyLoopPkg(5);
p.SaveToFile("demo7.dtsx");
p.Execute();
Console.Write(string.Format("Package executed with result {0}\n", p.ExecutionResult));
}
}
}
Note that in this example we reuse dataflow task in multiple loops. But only one instance of connection managers is created. This happens because EzAPI creates multiple wrappers for connection managers, but doesn’t duplicate connection managers themselves.
Extending EzAPI
Main types of objects currently supported by EzAPI are tasks, containers, packages, dataflow components, and connection managers. Let’s review how to extend EzAPI power by developing new EzObjects.
Developing Pipeline Components
The base class for all components is EzComponent. Let review how to create components using OleDBCommand transform as example. OleDBCommand has a lot of common with destination type of transforms. It requires mapping of input column to external metadata columns. That’s why we’ll use EzAdapter class as a base class. EzAdapter is a base class for all source and destination adapters.
// This attribute is mandatory. It should return GUID of the native component, of full type name
// of the managed component
[CompID("{8E61C8F6-C91D-43B6-97EB-3423C06571CC}")]
public class EzOleDbCommand : EzAdapter
{
// These two constructors are mandatory. The first one creates new component in SSIS dataflow
// The second one creates wrapper for the existing one
public EzOleDbCommand(EzDataFlow dataFlow) : base(dataFlow) { }
public EzOleDbCommand(EzDataFlow parent, IDTSComponentMetaData100 meta) : base(parent, meta) { }
// The third thing that is necessary is to declare properties specific to your component.
// Mostly they will look like the three properties above
public int CommandTimeout
{
get { return (int)m_meta.CustomPropertyCollection["CommandTimeout"].Value; }
set { m_comp.SetComponentProperty("CommandTimeout", value); ReinitializeMetaData(); }
}
public int DefaultCodePage
{
get { return (int)m_meta.CustomPropertyCollection["DefaultCodePage"].Value; }
set { m_comp.SetComponentProperty("DefaultCodePage", value); }
}
public string SqlCommand
{
get { return (string)m_meta.CustomPropertyCollection["SqlCommand"].Value; }
set { m_comp.SetComponentProperty("SqlCommand", value); ReinitializeMetaData(); }
}
}
Another example is DataConvert. Here is the code for it:
[CompID("{BD06A22E-BC69-4AF7-A69B-C44C2EF684BB}")]
public class EzDataConvert : EzComponent
{
public EzDataConvert(EzDataFlow dataFlow) : base(dataFlow) { }
public EzDataConvert(EzDataFlow parent, IDTSComponentMetaData100 meta) : base(parent, meta) { }
// This function sets convertion if inputColumn to the column with the specified datatype
public void Convert(string inColName, string newColName, DataType dataType, int length, int precision, int scale, int codePage)
{
LinkInputToOutput(inColName);
SetOutputColumnProperty(newColName, "SourceInputColumnLineageID", InputCol(inColName).LineageID);
SetOutputColumnDataTypeProperties(newColName, dataType, length, precision, scale, codePage);
}
// returns name of converted output column that corresponds to the specified input column
public string ConvertedColumn(string inputColName)
{
int lineageId = InputCol(inputColName).LineageID;
foreach (IDTSOutputColumn100 c in Meta.OutputCollection[0].OutputColumnCollection)
if (OutputColumnPropertyExists(c.Name, "SourceInputColumnLineageID") &&
(int)c.CustomPropertyCollection["SourceInputColumnLineageID"].Value == lineageId)
{
return c.Name;
}
return null;
}
// FastParse property set for the specified converted column
protected ColumnCustomPropertyIndexer<bool> m_fastParse;
public ColumnCustomPropertyIndexer<bool> FastParse
{
get
{
if (m_fastParse == null)
m_fastParse = new ColumnCustomPropertyIndexer<bool>(this, "FastParse", IndexerType.Output, false);
return m_fastParse;
}
}
}
This code is pretty straightforward and easy to understand if you are familiar with DataConvert component. The whole implementation took about 40 lines. Note that normally implementation of custom component simply includes exposing custom properties of component and, if necessary, input/output columns (like FastParse in DataConvert).
Developing Runtime Tasks
The base class for tasks is EzTask. To illustrate the process of task development let’s take ExecutePackage Task as an example.
// This attribute should be declared and return either Moniker, or full managed type name
[ExecID("SSIS.ExecutePackageTask.2")]
public class EzExecPackage : EzTask
{
// These two constructors are mandatory. The first one creates new task in SSIS control flow
// The second one creates wrapper for the existing one
public EzExecPackage(EzContainer parent) : base(parent) { }
public EzExecPackage(EzContainer parent, TaskHost task) : base(parent, task) { }
// Now declare properties. In most cases they will look similar to the properties below
public bool ExecOutOfProcess
{
get { return (bool)host.Properties["ExecuteOutOfProcess"].GetValue(host); }
set { host.Properties["ExecuteOutOfProcess"].SetValue(host, value); }
}
public string PackageName
{
get { return (string)host.Properties["PackageName"].GetValue(host); }
set { host.Properties["PackageName"].SetValue(host, value); }
}
public string PackagePassword
{
get { return (string)host.Properties["PackagePassword"].GetValue(host); }
set { host.Properties["PackagePassword"].SetValue(host, value); }
}
public string PackageID
{
get { return (string)host.Properties["PackageID"].GetValue(host); }
set { host.Properties["PackageID"].SetValue(host, value); }
}
public string VersionID
{
get { return (string)host.Properties["VersionID"].GetValue(host); }
set { host.Properties["VersionID"].SetValue(host, value); }
}
// We can only accept OLEDB or FILE connection managers.
protected EzConnectionManager m_connection;
public EzConnectionManager Connection
{
get { return m_connection; }
set
{
if (value == null)
throw new ArgumentNullException("value");
if (value.CM.CreationName != "FILE" && value.CM.CreationName != "OLEDB")
throw new IncorrectAssignException(string.Format("Cannot assign {0} connection to EzExecPackage task", value.CM.CreationName));
(host.InnerObject as IDTSExecutePackage100).Connection = value.Name;
m_connection = value;
}
}
}
In case of managed task everything looks pretty similar, except that ExecID returns full managed type name:
[ExecID("Microsoft.SqlServer.Dts.Tasks.ActiveXScriptTask.ActiveXScriptTask, Microsoft.SqlServer.ActiveXScriptTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")]
public class EzActiveXScript : EzTask
{
public EzActiveXScript(EzContainer parent) : base(parent) { EntryMethod = "Main"; ScriptingLanguage = "VBScript"; }
public EzActiveXScript(EzContainer parent, TaskHost task) : base(parent, task) { }
public string EntryMethod
{
get { return (string)host.Properties["EntryMethod"].GetValue(host); }
set { host.Properties["EntryMethod"].SetValue(host, value); }
}
public string ExecValueVariable
{
get { return (string)host.Properties["ExecValueVariable"].GetValue(host); }
set { host.Properties["ExecValueVariable"].SetValue(host, value); }
}
public string ScriptingLanguage
{
get { return (string)host.Properties["ScriptingLanguage"].GetValue(host); }
set { host.Properties["ScriptingLanguage"].SetValue(host, value); }
}
public string ScriptText
{
get { return (string)host.Properties["ScriptText"].GetValue(host); }
set { host.Properties["ScriptText"].SetValue(host, value); }
}
}
Developing containers and packages
The base class for containers is EzContainer. Here is how EzForLoop container can be implemented:
[ExecID("STOCK:FORLOOP")]
public class EzForLoop : EzContainer
{
public EzForLoop(EzContainer parent, DtsContainer c) : base(parent, c) { }
public EzForLoop(EzContainer parent) : base(parent) { RecreateExecutables(); }
public string AssignExpression
{
get { return (m_exec as ForLoop).AssignExpression; }
set { (m_exec as ForLoop).AssignExpression = value; }
}
public string EvalExpression
{
get { return (m_exec as ForLoop).EvalExpression; }
set { (m_exec as ForLoop).EvalExpression = value; }
}
public string InitExpression
{
get { return (m_exec as ForLoop).InitExpression; }
set { (m_exec as ForLoop).InitExpression = value; }
}
}
All packages derive from EzPackage class, which derives from EzContainer. Here is how a package with dataflow task can be created:
public class EzDataFlowPackage : EzPackage
{
public EzDataFlow DataFlow;
public EzDataFlowPackage() : base() { DataFlow = new EzDataFlow(this); }
public EzDataFlowPackage(Package p) : base(p) { }
public static implicit operator EzDataFlowPackage(Package p) { return new EzDataFlowPackage(p); }
}
Developing Connection Managers
Connection managers in EzAPI derive from EzConnectionManager class.
public enum FileUsageType : int
{
ExistingFile = 0,
CreateFile = 1,
ExistingFolder = 2,
CreateFolder = 3
}
[ConnMgrID("FILE")]
public class EzFileCM: EzConnectionManager
{
public EzFileCM(EzPackage parent) : base(parent) { }
public EzFileCM(EzPackage parent, ConnectionManager c) : base(parent, c) { }
public EzFileCM(EzPackage parent, string name) : base(parent, name) { }
public string DataSourceID
{
get { return (string)m_conn.Properties["DataSourceID"].GetValue(m_conn); }
set { m_conn.Properties["DataSourceID"].SetValue(m_conn, value); Parent.ReinitializeMetaData(); }
}
public FileUsageType FileUsageType
{
get { return (FileUsageType)m_conn.Properties["FileUsageType"].GetValue(m_conn); }
set { m_conn.Properties["FileUsageType"].SetValue(m_conn, value); }
}
}
Implemented SSIS objects
Tasks and Containers
- For Loop container
- DataFlow Task
- Execute Package Task
- ActiveX Script Task
DataFlow Components
- OLEDB Source
- OLEDB Destination
- FlatFile Source
- FlatFile Destination
- ADO.Net Source
- ADO.Net Destination
- Multicast Transform
- Derived Column Transform
- Sort Transform
- OLEDB command Transform
- Lookup Transform
- Cache Transform
- Data Convert Transform
- Aggregate Transform
Connection Managers
- OLEDB Connection manager (including specific versions for SQL Server, Oracle, DB2)
- FILE connection manager
- FLATFILE connection manager
- CACHE connection manager
- ADO.NET connection manager
Using template collections
Currently EzAPI framework includes a number of classes that can make development of packages even easier.
public class EzDataFlowPackage : EzPackage
Package with single dataflow task without any connection managers
public class EzForLoopDFPackage : EzForLoopPackage
Package with single ForLoop container with Dataflow task in it without any connection managers
public class EzSrcPackage<SrcType, SrcConnType> : EzDataFlowPackage
where SrcType : EzAdapter
where SrcConnType : EzConnectionManager
Package with single dataflow task that contains only source of SrcType and connection manager of type SrcConnType used by this source
public class EzSrcDestPackage<SrcType, SrcConnType, DestType, DestConnType> : EzSrcPackage<SrcType, SrcConnType>
where SrcType : EzAdapter
where SrcConnType : EzConnectionManager
where DestType : EzAdapter
where DestConnType : EzConnectionManager
Package with single dataflow task that contains source->destination dataflow and their connection managers.
public class EzTransformPackage<SrcType, SrcConnType, TransType, DestType, DestConnType>
: EzSrcPackage<SrcType, SrcConnType>
where SrcType : EzAdapter
where SrcConnType : EzConnectionManager
where TransType : EzComponent
where DestType : EzAdapter
where DestConnType : EzConnectionManager
Package with single dataflow task that contains source->transform->destination and connection managers for source and destination.
public class EzLoopTransformPackage<SrcType, SrcConnType, TransType, DestType, DestConnType>
: EzForLoopDFPackage
where SrcType : EzAdapter
where SrcConnType : EzConnectionManager
where TransType : EzComponent
where DestType : EzAdapter
where DestConnType : EzConnectionManager
Package with single dataflow task that contains source->transform->destination and connection managers for source and destination.
public class EzPkgWithExec<T> : EzPackage where T : EzExecutable
Package with a single executable (task or container)
public class EzExecForLoop<T> : EzForLoop where T : EzExecutable
Package with a single executable (task or container) in a for loop container
public class EzSrcDF<SrcComp> : EzDataFlow where SrcComp : EzComponent
Dataflow with some source component
public class EzSrcConnDF<SrcComp, SrcCM> : EzSrcDF<SrcComp>
where SrcComp : EzAdapter
where SrcCM : EzConnectionManager
Dataflow with some source component with connection manager
public class EzTransformDF<SrcComp, SrcCM, Trans> : EzSrcConnDF<SrcComp, SrcCM>
where SrcComp : EzAdapter
where SrcCM : EzConnectionManager
where Trans : EzComponent
public class EzTransDestDF<SrcComp, SrcCM, Trans, DestComp> : EzTransformDF<SrcComp, SrcCM, Trans>
where SrcComp : EzAdapter
where SrcCM : EzConnectionManager
where Trans : EzComponent
where DestComp : EzComponent
Dataflow with some source component with connection manager, transformation and destination without connection manager
public class EzTransDestConnDF<SrcComp, SrcCM, Trans, DestComp, DestCM> : EzTransDestDF<SrcComp, SrcCM, Trans, DestComp>
where SrcComp : EzAdapter
where SrcCM : EzConnectionManager
where Trans : EzComponent
where DestComp : EzAdapter
where DestCM : EzConnectionManager
Dataflow with some source component with connection manager, transformation and destination with connection manager.