SSIS CDC LSN Debugging nightmare first 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.