0

I have a problem where I try to read some data from QuickBooks Desktop file in SSIS through ADO.NET Source using QODBC driver. Driver is set up correctly, because I am able to preview table contents of the file like so enter image description here

I am also successful in using SQL Command access mode when I do a simple query like SELECT * FROM Item.
But the moment I try to use SQL Command to incorporate a WHERE clause against TimeModified field like so:
SELECT * FROM Item WHERE TimeModified >= '12/21/2022 1:16:34 PM'
and try to preview I get an error saying this: enter image description here
Now, I sent that row by using table access mode into a derived column and looked up what type TimeModified column uses and saw that it uses DBTIMESTAMP. So I suspected that you can't use the >= operator on that type, so what I also tried in my query is utilizing the DATE() function on the WHERE clause, omitting the time piece like so (Although ideally I would like to keep the hours to be able to use them in my query as time is important but for test sake): enter image description here But as you can see I got the Expected Lexical Element Not Found error.

I originally intended for this to work through an expression in which I obtain a value for a variable of most recent date by querying from a database prior this step and then get the items from the Item table that are older than said date. However, now I completely cut the expression idea just for the sake of trying to get this to work but it doesn't seem like it wants to. Am I doing something wrong? I made some querying in SQL against a date field by using strings of various formats like I showed earlier and those queries in SQL Server all succeeded but here nothing works.

If you are interested how the connection to QuickBooks Desktop is made I am using 32bit QODBC Driver which you can see there as 'QuickBooks Data' in the ADO.NET Source.

Thank you in advance for your help!

Coda759
  • 107
  • 14
  • Something to try. Cast on the right side of your where filter as a date. E.g., CAST('12/21/2022 1:15:34 PM' as DATETIME). Also, cast your TimeModified as a DATETIME so they are identical data types. – tonyd Dec 22 '22 at 15:16
  • 1
    @tonyd I tried ```SELECT * FROM Item WHERE CAST(TimeModified AS datetime) >= CAST('12/21/2022 1:15:34 PM' AS datetime)``` and it gives me this error: ------------------ There was an error displaying the preview ERROR [42000] [QODBC] [sql syntax error] Expected lexical element not found: = =, <>, <, <=, >, or >= (fqqb32.dll) – Coda759 Dec 22 '22 at 16:02
  • Looks like you may need to use the built-in QODBC proc SP_REPORTS. Not sure if SSIS will handle this correctly or not. See if these links are helpful: https://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2369/0/how-to-use-the-quickbooks-reporting-engine-with-qodbc https://stackoverflow.com/questions/42865455/custom-qodbc-query-in-quickbooks-for-excel-extraction – tonyd Dec 22 '22 at 17:05
  • @tonyd I experimented with using ```sp_report``` in my SQL Command and that does work. The thing is that I am pretty sure it just shows you actual reports of things and not exactly the table of contents if you will. The overall issue is, I can do standart SQL queries fine. And if I query ```SELECT * FROM ITEM WHERE ListId = 'aaa'``` it works fine executing and bringing me back 0 records. Its just the converstion from TimeModified column that I am struggling with – Coda759 Dec 22 '22 at 18:04

1 Answers1

2

So I figured it out. Apparently with QODBC when it comes to timestamps you have to query them by doing something like this:
SELECT * FROM Item WHERE TimeModified >= {ts '2022-12-16 15:05:55.000'} ORDER BY TimeModified DESC
Here is some more info: https://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2407/0/qodbc-desktop-troubleshooting-problems-with-date-format

Coda759
  • 107
  • 14