SSIS (visual studio 10, sql server 12 sp2) (OLEDB connection) into Vertica 7.1.2.0 loading issue

I am also having the same issue 

I used the ADO Vertica driver and it resolved the issue

Hello

Has anyone been able to solve this?  We are getting the same error.  ADO does work, but we would like to speed up our SSIS imports to Vertica with the OLEDB driver.  It is lightning fast going from Vertica to SQL Server, but I can't get it to work the other way around.

Joe

Hi,

The below guidance is from the Vertica EcoSystems Engineering team :

For SSIS, you should use either ODBC or ADO.NET. OLEDB doesn’t work with SSIS, only with SSAS.

thanks

Gayatri

Thanks Gayatri!

I really find that odd, since SSIS using the OLEDB driver can pull data from Vertica to SQL Server at speeds much faster than ADO.NET.  I guess it just doesn't work in reverse?  Is there a timeframe to integrate the OLEDB driver more fully into SSIS?

Thanks

Joe

The OLEDB driver was built to support SSAS only and it's not intended to be use with SSIS and SSRS. Vertica recommends ODBC or ADO.Net with SSIS. ODBC should perform better than ADO.NET and roughly the same performance as OLEDB (they both share the same codebase). But unfortunately ODBC do not offer the same level of customizability as ADO.NET, so there are sometimes errors when it comes to mapping SQL, data types, etc. from SQL Server to Vertica & vice versa. It is possible that you may not experience those issues, and in that case ODBC is probably better.