SSIS Ado.NET Source With Expression Fails Validation

Question

I have a weird problem where I have a control flow like so:

And at the end a data flow like so:

I am trying to read data from a QuickBooks Desktop file but to make sure I don't unnecessarily run the Data Flow task, I first compare the modified times from SQL Server and Quickbooks. Then if the record count comes back greater than zero, data flow task is executed. The expression in data flow task is as follows:

"SELECT ListID, [Name], CompanyName, TermsRefFullName, IsActive, Notes, TimeModified, TimeCreated FROM Customer WHERE TimeModified > {ts '" + @[User::CustomerMaxTimeMod] + "'} ORDER BY TimeModified DESC"

Where the CustomerMaxTimeMod is a string variable that stores maximum modified time I obtain in step 1 of the control flow.

I know this expression works because the data flow executes just fine when it is either first to go in the control flow or alone by itself. However, the problem is that when this data flow is a part of a greater control flow and is not first to execute, it then breaks saying that:

[ADO NET Source [2]] Error: An error occurred executing the provided SQL command: <my expression here> ERROR [42000] [QODBC] [sql syntax error] Expected lexical element not found: = <identifier>

[SSIS.Pipeline] Error: "ADO NET Source" failed validation and returned validation status "VS_ISBROKEN".

I have research this and have seen some people suggest to set delay validation property to true on the data flow. I tried that, along with trying to set validate external metadata to false. And it breaks anyways but with an error message that says this:

[ADO NET Source [2]] Error: System.Data.Odbc.OdbcException (0x80131937): ERROR [42000] [QODBC] [sql syntax error] Expected lexical element not found: = <identifier> at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader) at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute() at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper100 wrapper)

I am pretty much just shuffling between setting different properties to true or false at this point not knowing what to do. Any help is appreciated!

Answer 1

Well turns out in this particular case the problem was actually in the data that was coming in, as the expression for the QODBC driver requires the data to be in a timestamp format (yyyy-mm-dd hh:MM:ss.000). The SQL Server even though at first glance appeared to be storing data that way it was storing it in datetime and as a result the data it was trying to compare timestamp against was in wrong format so the expression in the ADO.NET source had to be written like so:

"SELECT * FROM Customer WHERE TimeModified >= {ts '"+
 (DT_STR, 23, 1252) (DT_DBTIMESTAMP) @[User::CustomerMaxTimeMod]  
+"'} ORDER BY TimeModified DESC"

Answer 2

What you're doing is totally valid and should work based on my mental parsing. A few things to note

Expressions on variables, not objects

Take the existing expression on your Data Flow's Odbc Source and create a variable, @[User::QuerySource] and use the expression in there.

"SELECT ListID, [Name], CompanyName, TermsRefFullName, IsActive, Notes, TimeModified, TimeCreated FROM Customer WHERE TimeModified  > {ts '" + @[User::CustomerMaxTimeMod]  + "'} ORDER BY TimeModified DESC"

Now in the Expression on [ADO NET Source] use @[User::QuerySource].

Seems like a nothing type of thing, it'll all net out in the wash but now, you can inspect the resulting query in a precursor step. When expressions are directly applied to objects, you cannot put a breakpoint and see what the rendered value is.

I make heavy use of a tiny script task that raises information events when then flow into the package execution log (assuming default logging levels)

bool fireAgain = false;
string message = "{0}::{1} : {2}";
foreach (var item in Dts.Variables)
{
    Dts.Events.FireInformation(0, "SCR Echo Back", string.Format(message, item.Namespace, item.Name, item.Value), string.Empty, 0, ref fireAgain);
}

Knob settings

Yes, as you have surmised, you need to have the DelayValidation set to True on the package as it's possible the design-time value of the expression is invalid.

I think that "validate external metadata" is only applicable to reducing the frequency of schema checks during design time. Run-time always has to validate metadata - either at the start of the package or immediately preceding execution in the case of DelayValidation.

Guessing at root cause

As the error you report is a syntax error and not the more common VS_NEEDSNEWMETADATA, I think that you're going to want to use the Script task to pop off the value of @[User::CustomerMaxTimeMod] and your query since it doesn't cost anything extra and pay attention to the format of the value of the max time. It sure smells like an unexpected or possibly empty value is getting in there and that's why you're getting a syntax error and not an invalid metadata.

You could also test this by adding an Execute SQL Task in there. Don't push the results into anything, but just verify that yes, you either do/do not get the same experience as the data flow (which again becomes easier as it's now driven by a variable and not the expression on object).