How to SET LOCKMODE SESSION WHERE LEVEL = MVCC on Ingres connection via SSIS

Can anyone please provide advice on how to enlist in an MVCC session from SSIS?

Reading from an Ingres DB, we have a requirement to enable MVCC and specify the isolation level from within an SSIS 2008 R2 package.

An existing application exists over this database, that DOES NOT use MVCC, and hence it is not appropriate to simply enable MVCC on the existing DBMS. The reason we want our reads to enlist in MVCC is to ensure we do not cause locks and break this existing application (as is currently periodically happening when we do not use MVCC to perform these reads).

DB version is Ingres II 10.0.0 (su9.us5/132)

ADO.NET driver version is Ingres.Client.IngresConnection, Ingres.Client, Version=2.1.0.0 driver,

We have a similar requirement to do so programmatically from within Tibco BusinessWorks, and interactively via eg SQL Squirrel, and meet this need by issuing the following commands via direct SQL execution (via JDBC):

SET LOCKMODE SESSION WHERE LEVEL = MVCC;
SET SESSION ISOLATION LEVEL READ COMMITTED;

In SSIS we can set the session isolation level using the IsolationLevel property of the task/sequence. But I can find no means of issuing the MVCC command directly.

I have attempted to issue the command via an Exceute SQL Task step, but I encounter the following error:

Syntax error on line 1. Last symbol read was: 'SET LOCKMODE'

What I've tried, to no avail:

With or without the terminating ;
Execute step placed within or outside of a sequence
Enabled the DelayValidation property, at both the sequence and step level
Various TransactionOption settings at the sequence and task level (in case they mattered!)
Setting the lockmode via a windows environment variable ING_SET = "SET LOCKMODE SESSION WHERE LEVEL = MVCC". But my testing shows this is not honoured by the ADO.NET driver we're using in SSIS (nor, incidentally, is it honoured by the JDBC driver we use for SQL Squirrel or Tibco). I believe this is probably an ODBC feature.
Issuing the command from within an ADO.NET source step within a dataflow. Same syntax error.
[UPDATE] Had also tried wrapping the SET ... commands in an Ingres procedure, but this resulted in syntax errors suggesting the SET ... command is not valid anywhere within a procedure.

Can anyone please provide advice on how to enlist in an MVCC session from SSIS?

At this stage (I believe) we're constrained to the ADO.NET driver, but if there's no other option that to go with ODBC then so be it.

Solution

Answering my own question here.

Two possible approaches were conceived.

1. Use a Script Component (within a Data Flow step)

From within a script component, I was able to issue the SET ... commands directly via ADO.NET.

The problem with this approach was that I wasn't able to retain the connection on which these commands had been run, for subsequent (or parallel, within the same dataflow) ADO.NET source components.

Attempting to work via a specific connection via transactions was no good, because these commands must be issued outside of an ongoing transaction.

So ultimately I had to also issue the source select from within this component, which even then is less than ideal as the subsequent destination insert operation could then not enlist in the same transaction as the source select.

The solution using this approach ended up being: - Using MVCC, copy the data from a source view, into a temp staging table on the source system. - Then using a transaction, read from the source staging table into the destination system.

Code looks something like this (NB had to explicitly add a reference to Ingres .NET Data Provider\v2.1\Ingres.Client.dll

/* Microsoft SQL Server Integration Services Script Component
*  Write scripts using Microsoft Visual C# 2008.
*  ScriptMain is the entry point class of the script.*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Ingres.Client;
using System.Collections.Generic;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    private bool debug = true;

    private IDTSConnectionManager100 cm;
    private IngresConnection conn;


    public override void AcquireConnections(object Transaction)
    {
        // The connection manager used here must be configured in the Script Component editor's "Connection Managers" page.
        // "Connection" is the (default) strongly typed name for the first connection added.
        // In this case, it needs to be a reference to the xxxxx connection manager (by convention it should be "xxxxx_ADONET").
        cm = this.Connections.Connection;
        conn = (IngresConnection)cm.AcquireConnection(Transaction);
    }

    public override void PreExecute()
    {
        debugMessage("PreExecute", "Started");
        base.PreExecute();

        string viewName = Variables.vViewName;

        IngresCommand cmdSetSessionLockMode         = conn.CreateCommand();
        IngresCommand cmdSetSessionIsolationLevel   = conn.CreateCommand();
        IngresCommand cmdReaderQuery                = conn.CreateCommand();

        List<string> sqlCommandStrings = new List<string>();
        if (Variables.vUseIngresMVCC)
        {
            sqlCommandStrings.Add("SET LOCKMODE SESSION WHERE LEVEL = MVCC");
        }
        sqlCommandStrings.Add("SET SESSION ISOLATION LEVEL READ COMMITTED");
        sqlCommandStrings.Add(String.Format("MODIFY {0}_STAGING TO TRUNCATED", viewName));
        sqlCommandStrings.Add(String.Format("INSERT INTO {0}_STAGING SELECT * FROM {0}", viewName));

        foreach (string sqlCommandString in sqlCommandStrings)
        {
            debugMessage("PreExecute", "Executing: '{0}'", sqlCommandString);

            IngresCommand command = conn.CreateCommand();
            command.CommandText = sqlCommandString;
            int rowsAffected = command.ExecuteNonQuery();

            string rowsAffectedString = rowsAffected >= 0 ? rowsAffected.ToString() : "No";
            debugMessage("PreExecute", "Command executed OK, {0} rows affected.", rowsAffectedString);
        }

        debugMessage("PreExecute", "Finished");
    }

    public override void CreateNewOutputRows()
    {
        // SSIS requires that we output at least one row from this source script.
        Output0Buffer.AddRow();
        Output0Buffer.CompletedOK = true;
    }

    public override void PostExecute()
    {
        base.PostExecute();

        // NB While it is "best practice" to release the connection here,  doing so with an Ingres connection will cause a COM exception.
        // This exception kills the SSIS BIDS designer such that you'll be unable to edit this code through that tool.
        // Re-enable the following line at your own peril.
        //cm.ReleaseConnection(conn);
    }

    private void debugMessage(string method, string messageFormat, params object[] messageArgs)
    {
        if (this.debug)
        {
            string message = string.Format(messageFormat, messageArgs);
            string description = string.Format("{0}: {1}", method, message);
            bool fireAgain = true;
            this.ComponentMetaData.FireInformation(0, this.ComponentMetaData.Name, description, "", 0, ref fireAgain);
        }
    }
}