Vertica & SSIS

scottpedersoli

We are using SSIS to import data into Vertica and are having a few intermittent issues. Wondering if anybody has experienced below problems or can recommend best practices when using Vertica/SSIS.

1. The SSIS package occasionally fails with error "The AcquireConnection method call to the connection manager Vertica failed with error." We see no errors on Vertica side.
2. We occasionally see wos_spill errors. In SSIS, inside of the data flow task, we are using a ODBC Destination component which does not allow us to use DIRECT hint.

The connection uses the Vertica driver which is version 7.02.01.00.

Sharon_Cutter

For the wos_spill part of this question - it's not a problem for data to spill from WOS to ROS unless it leads to "too many ROS containers" and loads fail (which would be caused by frequent loads that spill to ROS at a rate that the Tuple Mover can't keep up with). If you have only occasional loads, this is fine.

scottpedersoli

Thanks Sharon, good to know regarding wos_spill. We are not getting "too many ros containers" so I'm no longer as concerned with that. Regarding #1, after further digging i found this error in dc_errors: "New session rejected due to limit, already 125 sessions active". I find in sessions many records from users where the login_timestamp is from days/weeks ago. I have closed these. Do you have any experience with managing client connections? Wondering if it is better to set idle session timeout at the user level or cluster level?