SQL Server to Vertica Migration

1)  Are there any accelerators for SQL Server 2012 to Vertica migration?
2)  What is the best way to Import a SQL Server 2012 Table into Vertica.  Is Export SQL Server table to delimited FIle and use the COPY Command the only quickest option?


  • Options
    With pleasure and successful we used Talend DI to migrate from ms-sqlserver to Vertical (VertcaBulkExec component)
  • Options
    Thanks Massimo.  I suppose you used Talend for data migration.  How did you deal with the data structures (Tables, Views, Stored Procedures, Functions,...).
  • Options
    Talend can create table in Vertica with primary key and structure of the source table (disable automatic component projection creation) ... for functions you must rewrite it, for views you con create a projection, for stored procedure you must write UDX ....   see vertica doc for details .... at the end adop Vertica is the best choise I have ever made.
  • Options
    Navin_CNavin_C Vertica Customer
    Hi ,
    Other then the above solutions, if you dont want to go with TALEND ETL
    2. The best way to migrate a table :
    export table from SQL server--> land the files to Vertica Server--> run copy command
    Before running the copy command deploy the ddl into vertica. 

    For all this you create simple bash scripts and automate the process.

  • Options
    Thanks Navin.  That is what I am doing currently.  Using bcp to export the table as flat file and using COPY LOCAL (as we cannot store in Vertica Server) to load it in the table.
  • Options
    Navin, how did you handle the SQL Server Stored Procs?
  • Options
    Navin_CNavin_C Vertica Customer
    Best way to do this:
    Replicate the same logic in External procedure (.sh files)
    and schedule it with your Linux scheduler

    Hope this helps
  • Options
    Don - did you have to install the client drivers and vsql to the storage server location that you're copying from?  I'm trying to do the same thing - and I also cannot store the multi TB flat files on the Vertica cluster.  I'm struggling to figure out how to COPY from LOCAL in my environment.
  • Options
    Yes, I used an app server (:Linux) where I installed both SQL Server and Vertica drivers.  Using BCP I got the SQL Server Tables as Flat Files and used Vertica COPY LOCAL to load into Vertica.  Yes, it is common that you cannot stage the load files in the Vertica node.
  • Options

    question on sql stored procedures:


    from this thread i understand whether the stored proc can be converted to a .sh file or an function. My requirement is integrate the stored procedure to a UI ( like asp.net or WPF) because my stored procedure is returning a data set.  What is the best approach, can i call a vertica function through ADO.net ?


    I am using the vertica ADO.net connector by the way .

Leave a Comment

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