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 ODBC)	ADO	Flat File	Excel	SQL Mobile
Import/Export Wizard Source	-	Y	Y	N	Y	Y	N
Import/Export Wizard Destination	-	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 Work Tables	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.