Table of Contents

Introduction

In this section we'll explain what types of data sources enterprises need to integrate data from, and how SSIS supports this wide variety of data sources.

After a short description of data source categorization, you'll find links to specific data sources to have more about how that specific data source can be connected to SSIS. And finally, at the bottom of this article, you'll see the full list of available data sources for SSIS. 

Data Source Types and Categories

Data Sources are the source and destinations where SSIS can read/write data. In this section we will have a view of connectors (i.e. Microsoft OleDB provider for Oracle), capabilities (fast load), and tips & tricks per data source.

One way to categorize data sources is to separate them into logical chunks of how the target data is stored & processed. We can slice the data sources into Application Systems, Database Systems, Queue Systems and Protocols, and Unstructured & semi-structured data formats. Below, you'll see popular data source brands in each category and a link to more information about how SSIS can connect to these data sources.

DATA SOURCE CATEGORIES

Application Systems

  • SAP
  • Siebel
  • Microsoft Excel
  • Hyperion (Essbase, Planning, Financial Management)
  • Cloud Accounting Applications
    • FreshBooks
    • QuickBooks
  • Cloud Marketing Applications
    • MailChimp
    • Salesforce Marketing Cloud (ExactTarget)

Customer Relationship Management Systems (CRMs)

  • Salesforce
  • Microsoft Dynamics CRM
  • Zoho CRM
  • SugarCRM

Database Systems (RDBMS)

  • Microsoft SQL Server
  • Oracle
  • Sybase
  • IBM DB2
  • Teradata
  • MS Access
  • MySQL
  • PostgreSQL

Queue Systems & Protocols

An often asked aspect of SSIS: The list of protocols and queue systems SSIS can talk to:

  • MSMQ (Message Queue Task)
  • (s)FTP (FTP Task) **
  • HTTP/HTTPs (Web Service Task)
  • IBM MQ Series*
  • Tibco Rendezveus*
  • Unstructured & Semi-structured Data
    • Microsoft Excel, CSV
    • Text
    • XML
    • EDI
  • Ecommerce Applications
    • Bigcommerce
    • Magento

*not supported out-of-the-box, however, components can do this.

** sFTP is not supported out-of-the-box

 

Data Source Released By Data Access API Supported Database Versions Vendor Support with SSIS?§§ 64-bit support?
SQL Server Microsoft OLE DB 2000 and later Y x64, IA64
SQL Server Microsoft ADO.NET 2000 and later Y x64, IA64
SQL Server Microsoft ODBC 2000 and later Y x64, IA64
SQL Server Attunity OLE DB 2000 and later Y
SQL Server CozyRoc ODBC 2000 and later Y N
DB2 Microsoft OLE DB All DRDA compliant Y x64, IA64
DB2 IBM OLE DB z/OS and UDB 7.0 and later, AIX 5.0 and later, AS400 4.5 and later, Y N
DB2 IBM ADO.NET DB2 UDB 9.0 N N
DB2 IBM ODBC z/OS and UDB 7.0 and later,

AIX 5.0 and later, AS400 4.5 and later

 N N
DB2 Attunity OLE DB 6.1, 7.x, 8.0 on z/OS

7.x, 8.0 on UNIX

7.x, 8.0 on Windows

 Y
DB2/400 Attunity OLE DB On AS/400 5.1 and later Y
DB2 DataDirect OLE DB 7.1 through 9.1 on Linux, UNIX, and Windows

7.x through 8.1 on z/OS

V5R1 through V5R4 for iSeries (AS/400)

 Y x64
DB2 Devart Data Flow Component DB2 for LUW: 8.0 and higher Y Y
DB2 HIT OLE DB z/OS and UDB 8.0 and later, AIX 5.x, AS400 4.5 and later N N
DB2 ETI Bulk Load iSeries (AS/400), UDB 8.0 and later Y
DB2 [1] Persistent Data Flow Component - Bulk Write & Bulk Read - N
DB2 CozyRoc ADO.NET All Except UniData® and UniVerse® Y x64, IA64
DB2 CozyRoc ODBC - Y N
FreshBooks Devart Data Flow Component  All Y Y
QuickBooks Devart Data Flow Component All Y Y
Oracle Microsoft OLE DB 7.3.4 and later [2] Y N
Oracle Microsoft ADO.NET 8.0 and later Y x64, IA64
Oracle Oracle Corp OLE DB 8i and later Y x64, IA64
Oracle Oracle Corp ADO.NET 8i and later Y x64, IA64
Oracle Oracle Corp ODBC 8i and later N
Oracle Microsoft ODBC 8i and later N
Oracle Attunity OLE DB 9i and later Y
Oracle DataDirect OLE DB 8i through 10gR2 Y x64
Oracle Devart Data Flow Component Oracle servers 12c, 11g, 10g, 9i, 8i, 8.0 and 7.3, including Personal and Express editions  Y x64
Oracle ETI Bulk Load 9.0 and later Y
Oracle Persistent Data Flow Component - Bulk Write 8i & above N Y
Oracle DataDefractor ODBC 8i and later Y x64
Oracle CozyRoc ADO.NET 9iR2 to 11gR1 Y x64, IA64
Oracle CozyRoc ODBC 8i and later Y N
SAP Microsoft  SAP BI Connectors  SQL Server 2008 and higher

SAP Netweaver BI 7 and above 

   x64
SAP [2] Microsoft ADO.NET R/3 4.6C and later Y N
SAP Theobald Software OLE DB

- SAP Table Access, Queries, BW Cubes, BAPIs, ABAP Reports

 R/3 4.0 B or higher Y x64, IA64
Hyperion

Essbase

 Star Analytics Data Flow Component Essbase 6.1 and greater & System 9 Y
Hyperion

Planning

 Star Analytics

Y
Hyperion

Financial Management

 Star Analytics Data Flow Component HFM 3.4 and greater & System 9 Y
MailChimp Devart Data Flow Component All Y Y
Salesforce

Marketing Cloud (ExactTarget)

 Devart Data Flow Component All Y Y
Microsoft Dynamics CRM Devart Data Flow Component All Y Y
Salesforce CozyRoc Web Services - Read & Write - Y x64, IA64
Salesforce Devart Data Flow Component  All Y x64
SugarCRM Devart Data Flow Component  All Y Y
Zoho CRM Devart Data Flow Component  All Y Y
Access Microsoft OLE DB 2003 and earlier Y N
Access CozyRoc ODBC 2003 and earlier Y N
Excel Microsoft OLE DB 2003 and earlier Y N
Excel DataDefractor Low-level binary BIFF read; Open XML direct read; 2007 and earlier Y x64
Office 2007 Microsoft OLE DB 2007 N N
Office 2007 DataDefractor Open XML direct read 2007 Y x64
Sybase Sybase OLE DB 11.5 and later Y Y
Sybase Sybase ADO.NET 12.5 and later Y Y
Sybase Attunity OLE DB 12.0 and later Y
Sybase DataDirect OLE DB 11.x through 15.x Y x64
Sybase IQ Sybase OLEDB ? ? contact Sybase
Informix IBM OLE DB 7.3 and later N
Informix Attunity OLE DB 7.31, 9.x, 10 Y
Informix [4] Persistent Data Flow Component - Bulk Write & Bulk Read - N
Informix CozyRoc ADO.NET
Y x64, IA64
Informix CozyRoc ODBC 7.3 and later Y N
Teradata Teradata OLE DB 2.6 and later N N
Teradata Teradata ADO.NET 2.6 and later N Y
Teradata ETI Bulk Load 2.5 and later Y
Teradata ETI Bulk Extract 2.5 and later Y
MySQL CozyRoc ODBC 4.1 and later Y N
MySQL Devart Data Flow Component 3.23 and later including Embedded servers (starting with 4.1), 64-bit MySQL servers, Percona, and MariaDB Y Y
PostgreSQL CozyRoc ODBC - Y N
PostgreSQL Devart ADO.NET 7.1 and later Y Y
FoxPro Microsoft OLE DB 8.0 and later N N
File DBs Microsoft OLE DB Any Jet 4.0 compatible N N
Adabas Attunity OLE DB 6.2.2 to 7.4.x on z/OS

3.3 to 5.1 on Open Systems (UNIX, Windows, OpenVMS)

 Y
CISAM Attunity OLE DB On UNIX Y
DISAM Attunity OLE DB On UNIX, Linux, and Windows Y
Ingres II Attunity OLE DB 2 to 2.56 Y
Oracle Rdb Attunity OLE DB 7.1.x, on OpenVMS Alpha and Integrity (Itanium) Y Y (HP Integrity)
RMS Attunity OLE DB On OpenVMS Alpha and Integrity (Itanium) Y Y (HP Integrity)
Enscribe Attunity OLE DB On HP NonStop G-Series and H-Series Y Y (HP Integrity)
SQL/MP Attunity OLE DB On HP NonStop G-Series and H-Series Y Y (HP Integrity)
IMS/DB Attunity OLE DB 6.1 and later Y
VSAM Attunity OLE DB On z/OS 1.1 and higher Y
LDAP Persistent ODBC All LDAP compliant servers N Yes
Lotus Notes Team4 Custom Component ? ? ?
Lotus Notes Proposion .NET Connector ? ? ?
EBCDIC  Files Amino Software (Lysine) Custom component

- Requires SQL Server Integration Services 2005/2008

   Yes  ?
BigCommerce Devart   Data Flow Component All Y Y
Magento Devart    Data Flow Component All Y Y

Legend

[1] Product is in beta

[2] Although this provider can connect to and use versions of Oracle up to and including Oracle 10gR2, it does not support database constructs (such as BLOB/CLOB data types) introduced after Oracle 8.0)

[3] Product is in beta

[4] Product is in beta

 § What does "Vendor Support with SSIS" column mean?

YES in that column means that the vendor actually tested their solution with SSIS and can be contacted if there are any issues with their connector when used with SSIS.

NO means, we don't know, maybe - but it’s not confirmed. It’s a provider out in the market, and since SSIS supports standard interfaces like ADO.NET and OLEDB, it’s expected the provider might work with SSIS. But it has not been tested by Microsoft, and we don’t know if the vendor did any testing.

Note: This article has been ported over from http://ssis.wik.is/ Note: If you were the owner of above article, please update it to the latest or with other appropriate information.

Related Articles