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 ODBC)
ADO
Flat File
Excel
SQL Mobile
Import/Export Wizard Source
-
Y
N
Import/Export Wizard Destination
Execute SQL Task
Bulk Insert Task
Data Flow Source
Data Flow Destination
N[1]
N[2]
SQL Server Destination
OLE DB Command
Lookup Reference Tables
Fuzzy Lookup Reference Tables
Fuzzy Grouping Work Tables
Slowly Changing Dimension Outputs
Term Extraction Work Tables
Y[3]
Term Lookup Work Tables
Y[4]
Term Lookup Reference Tables
[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.
Maheshkumar S Tiwari edited Revision 3. Comment: Added tags