I am getting SSIS ETL error while running Copy Command to load data into Vertica

RajeshCh

Hi All,

I build one ETL package which is taking care of below steps.

1. It will get the data from Sql server Tables and generate .CSV file in shared folder.

2. After completetion of above step then immedaitlly Execute SQL task will initiate and COPY Command will run and loading the data from CSV file to VERTICA Table.

 Now in the above two steps first step got succeded but second step was failing due to below error. 

ERROR:

" [HY000] Unexpected protocol message: DataRow". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

 

I am unable to fix this. Could any one please help on this.

 

Thanks in advance.

 

Regards,

RajeshCh

avoronin

We follow this pattern 

1) run bcp on SQL server to create CSV 

2) convert csv file to UTF-8 (no bom byte)

3) copy to vertica server 

4) Run copy command from vertica 

 

it works. 

RajeshCh

Hi avoronin,

 

Thank you for your reply.

 

 If We ran that Copy command individually in VERTICA server it is working fine. But We are getting this HY000 error while running copy command through SSIS ETL package.

 

Please help me if you have any idea bout this.

 

Thanks & Regards,

RajeshCh

avoronin

make sure you have the latest vertica driver (.net provider)

make sure  CSV file has proper encoding UTF-8 (no BOM byte) 

make sure you understood what I wrote above 

avoronin

My command run from under SSIS 

 

SET LOCALE TO ru;

copy Facts.Stock2 from '/home/dbadmin/stock2_utf8.txt' DELIMITER E'\t' DIRECT;

=========

ConnectionString="database=...;host=...;port=5433;user=dbadmin;Pooling=False;">