SQL Server Source Patterns - SQL Server Integration Services Design Patterns - page 128

ODBC is the open database connectivity standard. Its purpose is to allow connections
from any application to any database, regardless of the vendor. Often, an organization
will use DSNs (data source names) to create an abstraction layer between the applica-
tion and the connection string the ODBC provider uses. If you have an organization
that really wants to use DSNs with SQL Server, ODBC is the option for you. Other-
wise, stick with an ADO.NET or OLE DB connection manager.
SSIS did not have an ODBC Source component prior to SQL Server 2012. Instead,
you could and still can use the ADO.NET connection manager with a few tweaks.
After you create the ADO.NET connection manager, change the provider at the top of
the window to Odbc Data Provider, as shown in Figure 4-3 .
Figure 4-3 . ADO.NET providers
Then add the DSN name or connection string. For our local AdventureWorks2012
database, the connection string will look like Listing 4-1 .
Listing 4-1 . ODBC Connection String
Driver={SQL Server Native Client 11.0};
Server=localhost;
Database=AdventureWorks2012;
Trusted_Connection=yes
Our completed Connection Manager screen for an ADO.NET connection with an
ODBC provider looks like Figure 4-4 .