Search code examples
ssisado.netvisual-studio-2022dataflowtaskqodbc

SSIS Ado.NET Source With Expression Fails Validation

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

enter image description here

And at the end a data flow like so:

enter image description here

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!

Solution

  • 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"