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:
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.
|SCCM||SQL Server DBA|
|Team Foundation Server||BizTalk Server Administrator|
Get Updates on Tech posts, Interview & Certification questions and training schedules