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.

  3 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.

  3. Thanks , above sample is useful to write custom sql task.

    I am developing a custom SSIS task for running sql task. But it fails with error – I’m trying to set the resultsetbinding for some parameters , to be used by next task in workflow.

    Error: 0xC0014054 at CustomSSISTask: Failed to lock variable “User::id” for read access with error 0xC0010001 “The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.”.
    Error: 0xC002F210 at CustomSSISTask, Execute SQL Task: Executing the query “SELECT id FROM sysobjects WHERE name = ‘sysrowsets…” failed with the following error: “Failed to lock variable “User::id” for read access with error 0xC0010001 “The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.”.
    “. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.
    Warning: 0×80019002 at CustomSSISTask: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
    Task failed: CustomSSISTask

    Here is the code sample I’m trying ,

    public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser,
    IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
    {
    MessageBox.Show(“testing:”);

    try
    {
    // Add the SQL Task
    Package package = new Package();
    package.Executables.Add(“STOCK:SQLTask”);

    Microsoft.SqlServer.Dts.Runtime.Variable variable = package.Variables.Add(“id”, false, “User”, 0);
    // Get the task host wrapper

    TaskHost taskHost = package.Executables[0] as TaskHost;

    // Get the task object
    ExecuteSQLTask task = taskHost.InnerObject as ExecuteSQLTask;

    // Set core properties

    task.Connection = connections[0].Name;

    task.SqlStatementSource = “SELECT id FROM sysobjects WHERE name = ‘sysrowsets’”;

    task.SqlStatementSourceType = SqlStatementSourceType.DirectInput;

    // Add result set binding, map the id column to variable
    IDTSResultBinding resultBinding = task.ResultSetBindings.Add();
    //IDTSResultBinding resultBinding = task.ResultSetBindings.GetBinding(0);
    resultBinding.ResultName = “variable”;
    resultBinding.DtsVariableName = variable.QualifiedName; //”User::id”;

    task.Execute(connections, variableDispenser, componentEvents, log, transaction);

    }
    catch (Exception ex)
    {
    throw new ArgumentException(ex.Message);
    }

    return DTSExecResult.Success;
    }
    It doesnt throw any exception but custom task fails. Issue seems to be VariableDispenser object doesn’t have User::id variable added and it fails to lock the variable. Am I missing something in code sample above?

 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>