Connect to SQL Server Compact Edition in SSIS

Question

Till now I've been exporting data INTO the SQL Server CE database, but now in SSIS I'm attempting to source data that was inserted in a previous step of the import.

The SSIS fails during validation with the following error

Error: Microsoft.SqlServer.Dts.Runtime.DtsCouldNotCreateManagedConnectionException: Could not create a managed connection manager.

I am attempting to use the ADO NET Source that connects to the Data Source using the Microsoft.SqlServerCe.Client.3.5 provider (i.e. ADO.NET connection manager). I CAN preview the data in the ADO.NET Source editor, but the package validation fails due to the above error.

Answer 1

Using SQL Compact as a source is not supported in SSIS. You must use other tools...

Answer 2

Yes it can, please refer to this article: http://msdn.microsoft.com/en-us/library/ayee3tzx(VS.80).aspx