API Sample – Conditional Split

This sample creates a data flow package with an OLEDB source feeding into a Conditional Split. Items of interest: The conditional split expression is set as a property on the transform’s output. The transform also has a default output. You will get a COM exception if you try to set an expression on the default…


API Sample – Row Count Transform

This sample creates a data flow package with an OLEDB Source that feeds into a Row Count transform. The Row Count transform is configured to store the result in the RowCountVar package variable. static void Main(string[] args) { Package package = new Package(); // Add Data Flow Task Executable dataFlowTask = package.Executables.Add("STOCK:PipelineTask"); // Set the…


API Sample – ADO.Net Source

This sample creates a data flow package with an ADO.Net source. Items of interest: The AccessMode property is similar to that of the OleDB Source, except it only has two values – 0 for TableOrViewName, and 2 for SqlCommand. Examples of both are included in the sample (one is commented out). To be able to…


API Sample – OleDB source and OleDB destination

  This sample creates a data flow package with an OleDB Source component which reads from a local AdventureWorksDW2008 database, and writes to a different table in the same database using an OleDB Destination. Items of interest: The AccessMode property is an int value which controls how you’re retrieving/sending data. Once you set a value,…


EzAPI – Alternative package creation API

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…


API Sample – Create a package with a data flow task

This code creates a package with a single data flow task. It will be used as a base for the more complicated code samples. static void Main(string[] args) { Package package = new Package(); // Add Data Flow Task Executable dataFlowTask = package.Executables.Add("STOCK:PipelineTask"); // Set the name (otherwise it will be a random GUID value)…


Samples for creating SSIS packages programmatically

This is an index post for the series of posts with examples on how to create packages programmatically. Creating and adding tasks to a control flow is pretty straightforward, but programmatically adding data flow components can be tricky due to the interaction with the native COM APIs. Each post will provide a simple scenario for…


Performance: Top 10 Best Practices

The SQL CAT team published a Top 10 SQL Server Integration Services Best Practices list back in October, and I thought I’d draw some more attention to it incase anyone missed it the first time around. The list contains performance related tips that were proven during the ETL world record performance run that was published…


How to create an installer for your custom extension

The simplest way to create an installer for your custom SSIS extension is by using a Setup Project in Visual Studio. Here are the steps we take when we’re creating installers for our Codeplex projects. 1. In Visual Studio, create a new Setup Project – File -> New Project… 2. Under Other Project Types |…


New connectivity white papers

Two new SSIS connectivity white papers have been published on MSDN. Connectivity Options for Microsoft SQL Server 2008 Integration Services by Martin Ellis This paper talks about the various connectivity options that SSIS supports, as well as the new connectors for Oracle, Teradata and SAP BI that were recently released. I was also happy to…