while loading a file into vertica 188.8.131.52 via SSIS, i am getting this below error:
[SSIS.Pipeline] Error: OLE DB Destination failed the pre-execute phase and returned error code 0x80004002.
I am also having the same issue
I used the ADO Vertica driver and it resolved the issue
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.
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.
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?
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.