• Article

Using Integration Services with SQL Server Compact

Microsoft SQL Server Compact is the compact database that enables you to extend enterprise data management and business intelligence capabilities to mobile devices. By using the Microsoft SQL Server Integration Services components designed specifically for SQL Server Compact, you can create Integration Services packages that write data to SQL Server Compact databases, and you can embed access to SQL Server Compact databases in applications.

This topic focuses on the Integration Services objects that are especially designed to work with SQL Server Compact. Integration Services also includes a wealth of objects to connect to SQL Server databases. You can extract and write data, execute SQL statements, and perform administrative database tasks. For more information, see Using Integration Services with the Database Engine.

Note

On a 64-bit computer, you must run packages that connect to SQL Server Compact data sources in 32-bit mode. The SQL Server Compact provider that Integration Services uses to connect to SQL Server Compact data sources is available only in a 32-bit version.

Connection Managers

A connection manager is a logical representation of a connection to a data source. When a package runs, the Integration Services runtime creates a physical connection using the attributes of the connection manager. Integration Services provides a variety of connection managers that tasks and data flow components can use to connect to databases and other types of data stores. For example, the Execute SQL task connects to a relational database to run SQL statements; the OLE DB source and destination extract data from and load data into databases; and the Lookup transformations access lookup tables stored in a database. However, only the SQL Server Compact Edition connection manager is specifically designed to work with SQL Server Compact databases.

SQL Server Compact Edition Connection Manager

A SQL Server Compact Edition connection manager enables a package to connect to a SQL Server Compact database. The SQL Server Compact Edition destination in SQL Server Integration Services uses this connection manager to load data into a table in a SQL Server Compact database.

For more information, see SQL Server Compact Edition Connection Manager.

Data Flow Destinations

Destinations are the data flow components that load the output of a data flow into different types of data sources, or create an in-memory dataset. Only the SQL Server Compact Edition destination is specific to SQL Server Compact.

SQL Server Compact Edition Destination

The SQL Server Compact Edition destination loads data into a SQL Server Compact database. You configure the SQL Server Compact Edition destination by specifying the name of the table into which the SQL SQL Server Compact Edition destination inserts the data.

For more information about this destination, see SQL Server Compact Edition Destination.

Integration Services icon (small) Stay Up to Date with Integration Services

For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN or TechNet:

For automatic notification of these updates, subscribe to the RSS feeds available on the page.