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

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

Comments

  • 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. 

  • 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

  • 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 

  • 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;">

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file