EzAPI Updated for SQL Server 2012

The EzAPI project has been updated for the SQL Server 2012 release. You can find the updated source code here. Changes Task and Component IDs updated for SQL 2012 For 2005/2008 support, please use a previous version of the code Project upgraded to .NET 4 EzProject – SSIS 2012 Project object model support Project Connection…


Handling HRESULT 0xC020402D from BufferManager.FindColumnByLineageID

While developing some custom transforms for my talks at the BI Conference next week, I hit a problem where I was getting an a failed HRESULT (COM Exception) in my PreExecute() method on a call to BufferManager.FindColumnByLineageID(). HRESULT 0xC020402D Which maps to DTS_E_NOBUFFERTYPEONSYNCOUTPUT. The error message for that exception is The %1 is a synchronous…


Looking up SSIS HResult / COMException ErrorCode

The method I describe in my post about handling COMExceptions during package generation works if you have control over the package generation code, but sometimes you’ll be using third party libraries, or debugging after the fact. Note, the error codes, symbolic names, and descriptions for all of the SSIS HResults can all be found on…


Debugging a COMException during package generation

You’ll occasionally receive a COMException when you’re programmatically generating SSIS package – typically when dealing with Data Flow components. These COM Exceptions will provide you with an HRESULT (ErrorCode), and no additional information. System.Runtime.InteropServices.COMException (0xC020801C): Exception from HRESULT: 0xC020801C    at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.AcquireConnections(Object pTransaction)    at MyApp.Program.CreatePackage()    at MyApp.Program.Main(String[] args) Ideally the exception you receive…


API Sample – Programmatically add a configuration file to a package

Update 2009-03-03: Darren Green has a post which covers the other configuration types as well. This sample programmatically adds a reference to an existing configuration file to a package. Things to note: The CreatePackage() method just creates a package (it doesn’t matter what is in it). Typically you’d be using this code to add a configuration…


API Sample – Lookup Transform

This sample creates a data flow package with an OLEDB Source component feeding into a Lookup Transform. The Lookup transform is set to Full Cache mode, and uses [DimCustomer] as its reference table. Items of interest: CustomerKey and GeographyKey are used as the index (join) columns. This is configured by using the JoinToReferenceColumn property The…


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,…