Creating a SSIS SQL Compact Data Source

There’s no out-of-the-box SQL Compact data source in SSIS, which presents a problem when you’re needing to copy data from a SQLCE data file.

 It turns out, though, that it’s easy to repurpose a OLEDB connection to read from a SQL Compact DB.

OLEDB is a generic connection method: so long as you know the correct connection string to use, you can manually edit the connection properties for another OLEDB connection.
Start out by creating a new OLE DB Connection in the connection manager. Right-click in the connection managers pane and select “New OLE DB Connection…”

Click on the “New…” button within the connection manager configuration window.
In the Connection Manager provider field, select “Native OLE DB\Microsoft OLE DB Simple Provider.” In the “Server or file name” field, enter a dummy file name. We’ll change this to point to the correct file in a moment.

Click on OK.
Now you’ll see your dummy OLE DB connection manager in the connection managers window. If you right click on it and select Properties, you’ll see the information below:

Now we can change the connection string manually.
The format for a SQL Compact connection string is as follows:

Data Source=path\to\filename.ext;Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;File Mode=Read Only;

The FileMode setting is optional, and I’ve not been able to have it make a difference when opening a SQL Compact file from within SSIS.
You’ll also want to change the name of the connection to something that is more descriptive.
Once you’ve changed the connection string (example below; sorry for the blurry image; click on it for a clearer view), you’re able to use this connection as a data source through the OLE DB Source toolbox.

Note that once you’ve made this change, you’ll no longer be able to double-click on the data connection manager to modify its settings. You’ll receive an error like this:

This isn’t an error to worry about: the problem is that the GUI isn’t set up to handle SQL Compact databases as an OLE DB connection. But it still will work within your data flow, both as a source and as a destination.



Another gotcha:  if you run into an error like this:

It's likely that you don't have read/write permissions on the SQLCE file.  SQL Compact requires read/write permissions on a file, even if you're not doing anything but reading from it.  (See this connect item for details.)