SSIS Connectivity White Paper
Data Source Compatibility of SSIS Components
In this section, we'll cover SSIS components and supported connectivity options in each component. It's worth mentioning the Connection Managers in SSIS, the components handling all different types of connection types including ADO.NET, OleDB, and ODBC, http(s), ftp, etc.
Below is the list of SSIS Data Flow Components and what each component supports in terms of connectivity. Please note that, unsupported items in the below matrix can still be supported using the extensibility model in SSIS.
Component |
SQL Server Only |
OLE DB |
ADO.NET (includes |
ADO |
Flat File |
Excel |
SQL Mobile |
Import/Export Wizard |
- |
Y |
Y |
N |
Y |
Y |
N |
Import/Export Wizard |
- |
Y |
N |
N |
Y |
Y |
N |
Execute SQL Task |
- |
Y |
Y |
Y |
N |
Y |
Y |
Bulk Insert Task |
Y |
N |
N |
N |
N |
N |
N |
Data Flow Source |
- |
Y |
Y |
N |
Y |
Y |
N |
Data Flow Destination |
Y |
Y |
N[1] |
N[2] |
Y |
Y |
Y |
SQL Server Destination |
Y |
N |
N |
N |
N |
N |
N |
OLE DB Command |
- |
Y |
N |
N |
N |
N |
N |
Lookup Reference Tables |
- |
Y |
N |
N |
N |
N |
N |
Fuzzy Lookup Reference Tables |
Y |
N |
N |
N |
N |
N |
N |
Fuzzy Grouping |
Y |
N |
N |
N |
N |
N |
N |
Slowly Changing Dimension Outputs |
- |
Y |
N |
N |
N |
N |
N |
Term Extraction Work Tables |
Y[3] |
N |
N |
N |
N |
N |
N |
Term Lookup Work Tables |
Y[4] |
N |
N |
N |
N |
N |
N |
Term Lookup Reference Tables |
- |
Y |
N |
N |
N |
N |
N |
[1] Although ADO.NET may not be used directly for loading database unless a custom SSIS script component is written, a data destination does exist for the ADO.NET DataReader object.
[2] Although ADO/place /> may not be used directly for loading databases unless a custom SSIS script component is written, a data destination does exist for the ADO Recordset object.
[3] The Term Extraction Work Table can also be a Microsoft Access table.
[4] The Term Lookup Work Table can also be a Microsoft Access table.
**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.