Dec 312008
 

From the SSIS development team, Matt Masson has posted a few blog posts on how to use the SSIS API. The posts use references to the 2008 version of SSIS, but to modify them for 2005 requires a simple change in most cases – Upgrading custom SSIS 2005 components to 2008.

You can find the blog posts here: http://blogs.msdn.com/mattm/archive/2008/12/30/samples-for-creating-ssis-packages-programmatically.aspx

Also, there is a post on using a new API framework for SSIS 2008, titled EzAPI: http://blogs.msdn.com/mattm/archive/2008/12/30/ezapi-alternative-package-creation-api.aspx

I haven’t looked into the EzAPI yet, but it certainly sounds interesting.

Let me know what you think about the new posts and if you’d like to see any thing else from the dev team and I’ll pass it along.

  2 Responses to “SSIS – Using the API”

  1. Hi, I am using EzAPI to generate SSIS package with a Execute script task followed by a Data flow transformation. I am getting error while assigning connection to SQL task, I believe ez execute sql task host is unable to cast to ExecuteSQLTask object. b.t.w
    ResultSetBindings property is not present by default, I manually changed the EzExecSqlTask code to expose IDTSResultBindings. Here is my code in Main function of a console application.

    EzPackage pkg = new EzPackage();
    //Create EzConnection and add it to package connections
    EzConnectionManager ezConn = AddEzConnection(pkg, “OLEDB”, @”.\SQLEXPRESS”, “AdventureWorks”);

    EzExecSqlTask sourceEntitySql = new EzExecSqlTask(pkg);
    sourceEntitySql.Name = “sourceEntitySql”;
    sourceEntitySql.Connection = ezConn; //Here is where exception occurs
    sourceEntitySql.SqlStatementSourceType = SqlStatementSourceType.DirectInput;
    sourceEntitySql.ResultSetType = ResultSetType.ResultSetType_SingleRow;
    sourceEntitySql.SqlStatementSource = “select City from [Person].[Address] where AddressID=?”;
    sourceEntitySql.BypassPrepare = true;

    sourceEntitySql.ParameterBindings.Add();
    IDTSParameterBinding parameterBinding = sourceEntitySql.ParameterBindings.GetBinding(0);
    parameterBinding.DtsVariableName = “User::AddressId”; //Global variable
    parameterBinding.ParameterDirection = ParameterDirections.Input;
    parameterBinding.DataType = (int)System.Data.OleDb.OleDbType.VarChar; parameterBinding.ParameterName = “0″;
    parameterBinding.ParameterSize = 255;

    sourceEntitySql.ResultSetBindings.Add();
    IDTSResultBinding resultBinding = sourceEntitySql.ResultSetBindings.GetBinding(0);
    resultBinding.ResultName = “commonEntity”;
    resultBinding.DtsVariableName = “User::City”;

    pkg.SaveToFile(“GenericCommonToLoadPackage.dtsx”);
    pkg.Execute();

  2. Hi Phil

    One of the useful components, that I found was missing from the 2008 implementation was the script component. Nice to see its added to the 2012 release. We simply created a custom component to implement the script we wanted, and added it to ezAPI. Quite simple. Dimodelo Architect use ezAPI to generate SSIS packages from the Data Warehouse design it captures. We have implemented some fairly complex ETL patterns using ezAPI. I’ve also written (yet another) getting started post at http://www.dimodelo.com/blog/2013/getting-started-with-ezapi/ . We take a slightly different approach to that explained on the SSIS blog post for ezAPI.

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>