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)
    • Salesforce
    • 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

*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

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

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

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

Salesforce

CozyRoc

Web Services - Read & Write

-

Y

x64, IA64

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

PostgreSQL

CozyRoc

ODBC

-

Y

N

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  ?

[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

SSIS Connection Managers
http://social.technet.microsoft.com/wiki/contents/articles/ssis-connection-managers.aspx

SSIS - Microsoft SQL Server Connectors
http://social.technet.microsoft.com/wiki/contents/articles/ssis-microsoft-sql-server-connectors.aspx

SSIS with Unstructured and Semi-Structured Excel, CSV Data
http://social.technet.microsoft.com/wiki/contents/articles/ssis-with-unstructured-and-semi-structured-excel-csv-data.aspx

SSIS: Excel Connectors
http://social.technet.microsoft.com/wiki/contents/articles/1954.aspx