Connecting to SQLite through SSIS

baumandm, , ,

This is a repost from my older blog—it was actually my most-popular post since writing in March 2009!  I was working on SQL Server Integration Services, trying to pull data from SQLite.  I found very little information online, and even less combining both SQLite and SSIS together.  The following worked for me in 2009, using SSIS 2008 and whatever version of SQLite was available then.  Hopefully this post continues to be useful!

After much investigation, I managed to access a SQLite database in SSIS through ODBC. Here are my steps:

  1. Go here: http://www.ch-werner.de/sqliteodbc/. Download the Current Version of sqliteodbc.exe
  2. Run this program, and it will install ODBC drivers for SQLite on your computer
  3. Open Administrative Tools and then Data Sources (ODBC)
  4. In the User DSN tab, click Add…
  5. Select the SQLite3 ODBC Driver and click Finish
  6. On the next page, type in a name for the Data Source, and pick the database file using the Browse button.
  7. Switch to SSIS and create a new Data Flow
  8. In the Data Flow, right-click in Connection Managers and create a New ADO.Net Connection
  9. Click Create to create a new Data Connection
  10. Select .Net Providers\Odbc Data Provider from the Provider dropdown
  11. Select the ODBC connection created previously, and Test Connection
  12. You can then add a ADO NET Source, connect it to that Connection, and load data from a table.

I hope this help somebody looking for the same thing as me.

Connecting to SQLite through SSIS

8 thoughts on “Connecting to SQLite through SSIS”

  1. MD says:

    Thanks for your post. I am getting an error message: “Architecture mismatch between the driver and application” . Any thoughts?

    Reply

    1. I haven’t seen that issue specifically, but it sounds like a 32-bit/64-bit discrepancy. The sqliteodbc driver site does mention that the 64-bit version of the driver will only work with 64-bit programs, so if you’re running a 32-bit program on a 64-bit OS, you would need to install the other version.

      Also, 64-bit Windows actually have two ODBC programs, one for each architecture. You might need to create the User DSN in the other program. Here’s a link that has the path for both programs: http://msdn.microsoft.com/en-us/library/cc645931.aspx

      Hope that helps point you in the right direction.

      Reply
    2. ryan says:

      I was able to resolve this by setting the solution Run64BitRunTime to “false” in the project properties under debugging

      Reply
    3. Amine says:

      I might be late but this may help others:

      Make sure you install both 64 and 32 bits Drivers by installing sqliteodbc.exe and sqliteodbc_w64.exe .

      Have a good day.

      Reply
  2. Chuck Moore says:

    Nicely done by all. Worked just as described. Used the comment from Amine to get over the last hurdle. I appreciate the effort in preparing the detailed instructions.

    Reply

Leave a comment