Database Reference
In-Depth Information
When connecting to external data, Integration Services uses a few objects to help make
the connection, retrieve the correct data, and start any necessary data manipulations.
Every time an Integration Services developer creates a package, the developer will
need to select the correct objects and ensure they have all been created. The objects
that will need to be set up are as follows:
Connection Manager:
The object that tells Integration Services
where to get data. Can be used in the control flow, data flow, and
event handlers.
Provider:
The object that the connection manager uses to talk to the
data source.
Source Component:
The object that sets the properties to tell Integ-
ration Services what data to get. The matching
Connection Man-
ager
object is set in this object.
Source Component Query:
The information the external data source
needs to give Integration Services data. The query is stored in the
Source Component
object.
Let's take a look at the important factors associated with each of these items. We'll
begin in the next section by looking at connection managers.
Selecting a SQL Server Connection Man-
ager and Provider
Between ADO.NET, ODBC, and OLE DB, there are enough connection managers to
make you want to pull your hair out! All of these connection managers will connect to
SQL Server, so how do you know when you should use which one? To answer that
question, let's talk about what the connection manager is actually doing and then look
at each connection manager type that you can use to connect to SQL Server.
A
connection manager
is the object that holds connection information for an ex-
ternal source, akin to an application data source or a Reporting Services shared data
source. The connection manager provides an abstraction layer between Integration Ser-
vices and the rest of the components so that information about the external source can
be modified in one place to affect all tasks and components. To see all of the connec-
tion manager types available, take a look at
Figure 4-1
.
Search WWH ::
Custom Search