1168
Part IX: Business Intelligence
■ (^) Initial Catalog: The default database in a multidatabase source
■ Security: Database authentication method and any username/password required
The fi rst choice for accessing databases has generally been an OLE DB connection manager
using one of the many native providers, including SQL Server, Oracle, Jet (Access), and a
long list of other source types. But with SQL Server 2012, Microsoft announced that OLE
DB is deprecated. ODBC is now being touted as the preferred access method. Other database
connection managers include the following:
■ ADO: Provides ADO abstractions (such as command and recordset) on top of the OLE
DB provider. ADO is not used by Integration Services built-in elements, but it could
be required by custom tasks written to the ADO interface.
■ (^) ADO.NET: Provides ADO.NET abstractions (such as named parameters, data reader,
and data set) for the selected database connection. Although not as fast as using
OLE DB or ODBC, an ADO.NET connection can execute complex parameterized
scripts, provide an in-memory recordset to a Foreach loop, or support custom tasks
written using C# or VB.NET.
■ ODBC: Enables a connection manager to be confi gured based on an ODBC DSN. This
is useful when OLE DB or .NET providers are not available for a given source (for
example, Paradox).
■ (^) OLE DB: The OLE DB connection manager was generally the preferred database con-
nection due to its raw speed. It provides methods for basic parameter substitution
but falls short of ADO.NET’s fl exibility.
■ Analysis Services: When accessing an existing Analysis Services database, this
connection manager is equivalent to an OLE DB connection using the Analysis
Services 10.0 provider. Alternatively, an Analysis Services database in the same
solution can be referenced — a useful feature for packages developed in support of
a new database. If one of the older OLAP providers is needed for some reason, it can
be accessed via the OLE DB connection manager.
■ Data Quality Services: Connection to Data Quality Services, enabling the Data
Cleansing task to cleanse columns in the data fl ow according to rules established in
DQS.
■ (^) SQL Server Mobile: Enables a connection to mobile database .SDF fi les.
Using File Connection Managers
The following are the many fi le confi guration managers:
■ (^) Flat File: Presents a text fi le as if it were a table, with locale and header options.
Flat fi le connection managers can now handle unequal numbers of columns. The fi le
can be in one of four formats:
■ Delimited: File data is separated by column (for example, comma) and row
delimiters (for example,{CR}{LF}).
■ Fixed Width: File data has known sizes without column or row delimiters.
c52.indd 1168c52.indd 1168 7/31/2012 10:29:28 AM7/31/2012 10:29:28 AM
http://www.it-ebooks.info