Can SQL Server Compact be used as both a Source and Destination in SSIS?

Question

I'm wondering if SQL Server Compact Edition can be used as both a Source and Destination in an SSIS dataflow. I know I can setup a SQLMOBILE connection manager, and I've found some information that mentions using it as a Destination, but nothing on using it as a Source.

What I'm looking to do is to transfer data from one SQL Server Compact file to another.

Answer 1

SQLMOBILE is only available as a destination. If you need to transfer all data, just make a file copy...

Answer 2

It can be done using OLE DB.

The problem is that Sql Server CE is not supported by SSIS User Interface to create OLE DB Connections.

But it's possible to create a OLE DB connection for anything, and then manually change the connection string, using the Property Window. And of course, renaming the connection.

The connection string can be something like:

Data Source="D:\path\mydb.sdf";Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;

of course, the Provider field must match the SQL Server CE version to be used.

Creating a OLE DB Connection for every sdf database file it' possible to create all OLE DB Sources and Destinations needed to perform the data transfer.

Hope this helps. :)

Answer 3

This solution works for the SQL Server 2008 but not for the Sql Server 2005. Which shows the following message when trying to access its tables.

'Microsoft.SQLSERVER.CE.OLEDB.3.5' provider is not registered on the local machine.

Any Ideas??