Other Heterogeneous Sources
Beyond the heterogeneous data already discussed, you may come across other non-SQL Server systems to which you need access. Examples of this include DB2 or Teradata or applications like SalesForce, SAP, or PeopleSoft or even decision support systems (DSS). A general approach in connecting to these is to first search for an OLE DB provider, so that you can then use the OLE DB adapters in the Data Flow to extract and load data to the system.
The Codeplex SSIS Community Tasks and Components list gives several of the available third-party connection managers (free and paid) that you can use with SSIS at http://ssisctc.codeplex.com/.
Also, if you need to connect to an IBM DB2 system, an OLE DB provider is available from Microsoft at http://msdn2.microsoft.com/enus/library/Aa213281.aspx. This provider was originally used in the Host Integration Server but has been made available for broad use.
For SAP connectors, Microsoft has historically provided them with the SQL Server feature pack, which can be found on the Microsoft download site (www.microsoft.com/download). At the time of writing, the feature pack has not been released for SQL Server 2014, but these will be available when released.
If an OLE DB provider is not available from Microsoft, you can always check the company that owns the system to see if they provide a free OLE DB or ODBC driver. Be aware that sometimes it is not in their interest to make it easy to connect to their systems, so even if they do have a provider, it may be slow. Alternatively, some software companies sell providers. The following is a list of companies to research that can assist in expanding your connectivity options:
- Attunity (www.attunity.com) has several SSIS connectors including Teradata, BizTalk, and SharePoint. In SQL Server 2008, Microsoft licensed the use of the Oracle and Teradata Attunity connectors for SSIS and provided a free download from the Microsoft download site. As of this writing, the SSIS 2014 components have not yet been released, but check the Microsoft download site (www.microsoft.com/download) for latest updates.
- CozyRoc (www.cozyroc.com) has dozens of SSIS connectors, tasks, and transformations that you can use with SSIS, including connectors to Amazon S3 cloud, Microsoft Dynamics, and SharePoint to name a few.
- Data Direct (www.datadirect.com) sells data connection providers (ODBC and OLE DB) that can be installed on Windows operating systems. Some of the connections include Sybase, IBM DB2, Teradata, Informix, and Lotus Notes.
- Pragmatic Works (www.pragmaticworks.com) sells a product called Task Factory that is a collection of custom SSIS components including a destination for Oracle.
NOTE Some systems offer only APIs that enable you to connect to the data programmatically. In these cases, you can also use the Script Component as a source and leverage the system API. See Scripting in SSIS Topic for more information about leveraging the Script Component.