Someone here at work is going to use SQL Server Compact Edition (SQL CE) to do some data storage, so I wanted to check it out as well since I haven’t used that version before. Google shows some folks having problems connecting to it and pushing/fetching data via SSIS, some links below for reference. Since Vb.Net and c# connectivity are well documented, here’s how to via SSIS using delivered tasks with no custom code.
Drag a Data Flow Task to the Control Flow tab, and then go to the Data Flow Tab. Notice that there is a SQL Server Compact Destination data flow destination, but nothing for SQL Server CE.
Image 1 - Data Flow Tasks
You can also look at the Solution Explorer, Data Sources (right-click), and see that in the .Net providers there are (in my case) three references to a SQL Server Compact Provider. I couldn’t use this method to get a connection manager to work, maybe you can.
Image 2 - New Data Source from Data Sources in Solution Explorer
Where I was able to connect was simply by right-clicking at the bottom in BIDS and creating a “New Connection”, scroll, and choose SQLMOBILE. This I think is where everyone doesn’t check, either that or they forget that SQL Mobile and SQL Server CE are pretty much the same thing I believe, although I could be wrong.
Image 3 - Create a new Connection Manager
Image 4 - Scroll to SQLMOBILE
Go to the .sdf file and add the password.
Image 5 - Connect to .sdf file
From here, it’s like any other connection. I’ve connected and sent data to SQL Server CE using this method.
Image 6 - Package running
Clicking the connection manager, note that you have to create the table in SQL Server CE first, before moving data; you can’t as far as I can tell, create the table on the fly as you can in other SSIS tasks.
Image 7 - Advanced Editor for SQL Server Compact Destination
Next time I”ll show you how to fetch data from SQL Server CE; it’s a bit different process than this one, as you’ll notice that there’s no Data Flow Source task that accompanies the SQL Server Compact Destination task.
Thanks,
Lee
------------------------------
Difficulties connecting that I found…
http://stackoverflow.com/questions/1308943/ssis-2008-how-to-read-from-sql-server-compact-edition-file
http://stackoverflow.com/questions/2181496/can-sql-server-compact-be-used-as-both-a-source-and-destination-in-ssis
http://stackoverflow.com/questions/2517035/connect-to-sql-server-compact-edition-in-ssis
http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-dts/12542/SSIS-and-SQL-CE
a3837e0f-e6cb-48f4-8e1c-1ac98d15a11c|1|5.0