Wed 31 Dec 2008
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.
July 26th, 2011 at 3:27 pm
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();