Aaron Lowe

Continuing the CDC debugging posts:

• Intro
• First Problem

Now that I have the StartLSN and EndLSN I was ready to go.  Hit my breakpoint and the variables were set appropriately.  Woo-hoo!

Then…wait a minute…what?

vSQLQuery.Value =
    "SELECT " +
    "    c.__$operation " +
    "    , c.Column1 " +
    "    , c.Column2 " +
    "    , ColumnN " +
    "FROM " +
    "    cdc.fn_cdc_get_net_changes_<instance_name>( " + sStartLSN + ",  " + sEndLSN + ", 'all') c";

That statement wasn’t changing the value of vSQLQuery.  huh?

Ok, I don’t by any stretch of the imagination claim to be an expert at C# but that looked pretty straight forward to me, however it wasn’t changing the value.

So now I start looking in earnest, google, bing, Stackoverflow, Serverfault, all to no avail.  This just wasn’t making sense.  I was about to call over an expert C# person (Magenic is full of them ) when I remembered that my previous problem was not a problem with the code but a problem with the configuration of the task.  So I start looking into the configuration of the script task and the variables that I was using and that’s where I saw it. 

vSQLValue was set to evaluate as an expression.  I had done this so I could copy and paste the SQL query I had developed in SSMS as opposed to making the query 1 line and pasting it into the value of the variable (the variable box in SSIS can’t interpret multi-line values, it only pastes the first line).

Turned off the fact that it was an expression and since it had already been evaluated it set the variable correctly (score!) and now I could manipulate the vSQLQuery.Value via the script task.

Now as to why I couldn’t modify a variable that is set to evaluate as an expression, I don’t know I’m sure there’s some sort of order of operations or locking catch here, but I still haven’t been able to find anything official about this.  Best I saw was this which would indicate to me that it would work.

Onto the next problem.

So now that we have the idea let’s look as the four main problems I encountered:

The first problem I noticed was that the SQL query that was running still had nulls in on the StartLSN and EndLSN

so I was querying:

cdc.fn_cdc_get_net_changes_<instance name> (null, null, ‘all’)

Which as I said last post, returns an error. So throw a break point into the Script task and figure out why it’s not setting properly.  This took me awhile, here’s the code:

var vStartLSN = Dts.Variables["User::sStartLSN"];
var vEndLSN = Dts.Variables["User::sEndLSN"];
var vSQLQuery = Dts.Variables["User::sSQLQuery"];

string sStartLSN;
string sEndLSN;

Dts.VariableDispenser.LockForRead("User::sStartLSN");
Dts.VariableDispenser.LockForRead("User::sEndLSN");
Dts.VariableDispenser.LockForWrite("User::sSQLQuery");

sStartLSN = vStartLSN.Value.ToString();
sEndLSN = vEndLSN.Value.ToString();

vSQLQuery.Value =
    "SELECT " +
    "    c.__$operation " +
    "    , c.Column1 " +
    "    , c.Column2 " +
    "    , ColumnN " +
    "FROM " +
    "    cdc.fn_cdc_get_net_changes_<instance_name>( " + sStartLSN + ",  " + sEndLSN + ", 'all') c";
Dts.Variables.Unlock();

So hitting my first breakpoint, I first realized that the StartLSN variable was receiving a null.  That wasn’t supposed to happen, so I looked at the SQL code I used to set the variables in the SQL Task:

SELECT sys.fn_cdc_get_min_lsn(<instance_name>) as @StartLSN

A-ha, that’s it. The function returns a record set and I had the task to just get a return value, not a record set.

So changed to:

SELECT @StartLSN = sys.fn_cdc_get_min_lsn(<instance_name>);

SELECT @StartLSN as StartLSN

That got me past the first problem, now onto the next problem.