When attempting to write to SQLite data file using ODBC, all the records are indicated as errors, and nothing is written. I tried doing this both with and without a data-driven database write strategy. I just want to insert a table for the records that result from this process. I can successfully write to the table directly with ODBC and the MSQUERY tool. I have also examined the ODBC configuration for the SQLite table, but I see nothing that is out of place.
Try attaching a record-level log to the output of your target database table. This will record and reveal the errors you’re encountering.
Cool feature.
The problem looks like it might be the underlying SQL generated for the insert, but I could be wrong.
Try playing with the ODBC options on the connection screen. We have not tested with SQL Lite to this point so there may be some quirkiness here.
I cannot see that as an option - either on the database destination or the database browser; would that be because I am using the community edition?
I have found that when dragging over ODBC linked text files from the database browser, the resulting source in the data flow has oddly constructed SQL and it does not function. Although I have other methods of accessing the file from the toolbox, this makes me wonder if the writing issue with SQLite is similar.
Hi,
Those options are also there on the Community Edition. Can you provide me a screenshot of what the query looks like and why it looks odd?
That was the trick!! Thank you so much. I didn’t see those icons at first, but clearly, they are there.
It works with Double Quotes instead of the default. The attached screenshots show the before and after attempts. It also resolves the curious construction of the SQL.
I am enjoying this software the more I use it. It gets the job done and makes work life so much simpler - beats trying to get things done in Excel, and it works time after time without having to figure out where manual edits were done on the data.
