Importing Data into SQL Server Compact

SQL Server 2005
In SQL Server Management Studio, when you right-click on a database, you will notice that you can choose to import or export data.

SQL Server 2005 Compact Edition

Each file is a database. When you connect to a SQL Server Compact Edition database, you will notice that when you right-click, you do not see many options, including the ability to import or export data.

If you wish to import data from an external source (E.g. importing data from a SQL Server 2005 table), you will have to either write your own .Net code to do the importing OR you can make use of SSIS.

Though I like the standardized way of connecting to a SQL Server Compact Edition database via Management Studio, I wish that I will have an easier way to import/export data from external data sources.

Here's what I did to import the data from a SQL Server 2005 table to a SQL Server Compact Edition table..

1. Create an Integration Service project in VS2005


2. Create two Connection Managers - one for connecting to the SQL Server 2005 table, and the other one to connect to the SQL Server Compact Edition table
   - In the Connection Manager panel, right-click, and Select to Add a Connection
   - After you installed SQL Server Compact Edition, you will notice that there is a Connection Manager Type for SQL Mobile (i.e SQL Servcer Compact Edition). You can use that to connect to the SQL Server Compact Edition database.


3. From the data flow Source, choose to create a OLE DB Source


4. From the data flow Destination, choose to create a SQL Server Compact Edition Destination.


5. Once completed, link the output of OLE DB Source to the input of the SQL Server Compact Edition Destination.

Check out the diagram to see the complete data flow. Run it, and you can now import data into a SQL Server Compact Edition table effortlessly.