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

Chandra_Prakash

Hello All,

while loading a file into vertica 7.1.2.0 via SSIS, i am getting this below error:

[SSIS.Pipeline] Error: OLE DB Destination failed the pre-execute phase and returned error code 0x80004002.

 

please help!!

 

 

Regards,

CP

Raj_G

I am also having the same issue 

Raj_G

I used the ADO Vertica driver and it resolved the issue

JoeGonzalezSSI

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

 

Gayatri_R

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

JoeGonzalezSSI

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

Connyrt-emp1

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.