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

RajeshChRajeshCh Employee, Registered User

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

  • avoroninavoronin Registered User

    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. 

  • RajeshChRajeshCh Employee, Registered User

    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

  • avoroninavoronin Registered User

    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 

  • avoroninavoronin Registered User

    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