SSIS 2008 - How to read from SQL Server Compact Edition file?

Question

I can see "SQL Server Compact Destination" under Data Flow Destinations, but I am looking for its source counterpart. If I choose ADO.Net source and create a new connection, there's no provider for SQL CE. What am I missing?

Thanks!

Update: I am able to create a "Data Source" (under "Data Sources" folder in my SSIS project") that connects to an existing Sql CE file. But how can I use this Data Source in my data flow?

Answer 1

You can create a OLEDB connection for anything, and then manually change the property ConnectionString in the "Properties" panel.

for my package I used

Data Source="C:\Path\myDb.sdf";Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;

Answer 2

The solution is to drop to C# or VB in a script task. :(