Database Reference
In-Depth Information
An example scenario would be to have a number of branch offices that upload files to a corporate folder. You
could process these files one by one into a staging table by combining the file connection manager with an SSIS
For-Each container, which allows you to loop through multiple files one by one and apply processing tasks to
each of them in turn.
he OLE DB Connection Manager
The OLE DB connection manager is the most common connection manager you will use, since it allows you
to connect to anything that has an OLE DB provider. That is a very wide range of connection options when you
consider that anything from an Excel spreadsheet to an old mainframe database will have OLE DB providers
available.
The OLE DB connection manager is designed to be flexible when working with data types that differ from the
originating and destination data sources, and because of this built-in flexibility, the OLE DB connection manager
is the one that gives you the least amount of trouble when it comes to data conversion error.
■
ssis has a picky attitude toward data conversions, which can be quite frustrating. These issues are less
likely to occur when using oLE DB connections. This is an important consideration when choosing to use oLE DB
connections in your ssis packages!
Note
As usual, a gain in flexibility comes at a cost. The cost in this case is a decrease in speed. As a result, for
raw performance (when your project requires speed), you probably want to use a more specialized connection
manager such as the ADO.NET connection manager, for example. And for ease of use and compatibility (for
comparatively smaller projects), use the OLE DB connection manager.
If you are working with a mere several thousand rows, we recommend using an OLE DB connection
manager. With only thousands of rows (as opposed to millions), you will notice very little difference in
performance and will appreciate the flexibility and ease of use of the OLE DB connection manager.
The ADO.NET Connection Manager
The ADO.NET connection manager is preconfigured to use a .NET provider for accessing data sources, and
depending upon the size and type of data you are working with, it may give increased performance over the
generic OLE DB connection manager. The performance increase when used with the newer versions of SQL
Server can be substantial, but with older or non-Microsoft databases, there are little to no performance gains, no
matter how much data is involved.
The ADO.NET connection manager is limited to the types of connections it can make, specifically in
comparison to the OLE DB connection manager, but it connects to all versions of Microsoft SQL Server.
The data types used in an ADO.NET connection manager are much more specific to Microsoft's .NET data types.
As you work with them, you begin to see that the list of types looks very different than the more generic OLE DB
data types, which are based on an ANSI standard and not the .NET standard. This is important because SSIS creates
metadata to describe all of its source, destination, and transformation components. If the metadata of your SSIS task
does not identify a compatible data type, your task will either process with a warning or fail to process at all.
■
we recommend using the oLE DB connection manager whenever possible since it provides the great-
est flexibility and the least amount of problems, which is especially important when you are first learning to use a
complex tool like ssis. To be clear, there is nothing wrong with the more precise .nET data types or the ADo.nET
Note
