DB2 Source Patterns - SQL Server Integration Services Design Patterns - page 171

Pick Provider Vendor
Although it is possible to use ODBC and ADO.NET to connect to a DB2 database, we
will focus on OLE DB providers in this chapter to ensure that we can use the connec-
tion for all transformations. Here are two of the more common providers and when you
would use each one.
IBM OLE DB Provider for DB2: IBM produces their own OLE DB
provider, which can be used in applications such as Integration Ser-
vices. This provider can be used for all versions and the latest
products.
Microsoft OLE DB Provider for DB2 Version 4.0: Microsoft cre-
ated a provider that uses OLE DB to connect to DB2. This provider
can be used for all versions of DB2. See the latest documentation for
which product numbers it supports.
Don't forget to make sure you've selected either the 32-bit or 64-bit version, based
on the database server. Also ensure that the database version matches the supported
version and product for the provider you want to use. We recommend using the pro-
vider most often used in your organization to facilitate ease of development and main-
tenance. If you are trying a provider for the first time, try the different versions to see
what works best for you, since the performance and security discrepancies may vary
per environment.
Connecting to a DB2 Database
For this chapter, we'll use the Microsoft OLE DB Provider for DB2. No matter what
provider you choose, the next step is to make a connection to the DB2 database. To do
this, you need to create a connection manager, select the correct provider, and fill out
the appropriate server information.
Once you download your desired provider, you will install it on the server where
you will develop and execute your Integration Services packages. If the provider has
installed correctly, you can see it by opening up the Source Assistant. A correctly in-
stalled provider can be seen in Figure 6-3 .