SSIS Vertica as destination TIMESTAMP columns

sorenhakola

Hi,

First:
Vertica version: Vertica Analytic Database v11.1.1-11
Visual studio version: 2022
SQL Server Integration Services Project: 1.3.2

I've been playing around with SSIS and Vertica as a destination. As suggested, I'm using ADO.NET as the driver type for both Vertica source and destination. Although I have also tried ODBC and OleDB just in case.

Problem:
When inserting into column of type TIMESTAMP, the row is never written, nor throws the package any errors and no rows are redirected as error rows. The log from the debug session (the text in the output text window) also states 0 rows written.

Receiving column type in Vertica is plain TIMESTAMP / DATETIME, not TIMESTAMP(3) or anything that would restrict the data.
I have also ensured that the incoming data pipeline's column type is DT_DBTIMESTAMP and that SSIS is recognizing the Vertica's datatype as DT_DBTIMESTAMP.

What I've tried:
Tests have been done with both text files and databases (SQL Server, Oracle) as sources with the same result.

I've tried changing the data types to incoming (DT_WSTR, 50) (i.e. unicode string) and ensured that the format is ok. Still can't write data.

When outputting the data to a text file, and the format of the timestamp is the same as in the (DT_WSTR,50) test, and using the COPY command to bulk load the data, the data is written fine into Vertica. I.e. the blocking happens somewhere in the drivers (ADO.NET, OleDB, ODBC).

Questions:
Is it even possible to write TIMESTAMP data in SSIS?
if it is, how is the destination supposed to be configured to be able to insert data of type TIMESTAMP? It would be really neat to be able to write directly to Vertica instead of via files.

Best regards,
Sören