Connection Leak Issue with SSIS 2014 & Postgres ODBC

1
votes

I'm working on a project for a client that involves a pretty huge ETL process to move data from MSSQL Server to Postgres. We are using SSIS 2014 with the ODBC drivers provided on postgresql.org, and have setup an ODBC DSN. We are only using built in OLEDB Sources and ODBC Destinations, and we are running into an issue that I have not been able to find referenced anywhere else online.

The exact issue is that SSIS seems to open multiple connections for each ODBC destination data flow component, even with connection pooling enabled. This can result in 50+ idle connections being opened, which are not killed until the process is completed. Previewing data from Visual Studio causes connection leaks as well which will only be killed upon restarting Visual Studio. We have temporarily resolved this by increasing the maximum connections to 1000, but we are hoping to fix the underlying problem if possible.

I've done a decent amount of experimentation, and the issue seems to be SSIS related as opposed to an error in the driver.

Has anyone else run into a similar issue and know how to resolve this?

EDIT: Thought this wasn't going to be a big issue, but now I realize that ODBC connections are leaking when the SSIS package is run from Integration Services as well. I've played with it a bit by making some empty packages and adding only a single ODBC source and also a package which only accesses the database from C# script task; only the ODBC source/destinations are causing leaking and not script components, so it seems like a bug in SSIS and not my script tasks or the postgres driver :O

Anyone have any idea how to resolve this besides rewriting the whole package to not use ODBC sources/destinations (or some other weird thing like killing all connections afterwards with a shell script)?

1
sql-serverpostgresqlssisodbcconnection-leaks
Does this actually cause an error, or are you just concerned? SSIS wants to run in parallel by default - this is usually a good idea on modern servers. For my "huge ETL processes" I try to encourage this in my package design, with the objective of completing the execution as quickly as possible. – Mike Honey
Ah, I hadn't considered how the parallel natural of SSIS would cause it to need multiple connections. I find it odd that it never closes any though. For example, if you open a "View Existing Data" on a ODBC destination, this will open a couple new database connections which will not be closed until restarting Visual Studio (this example is only an annoyance of course). The only legitimate concern is with 50+ idle connections, you run the possibility of hitting the connection limits on our postgres box, but I suppose those could be increased to reduce the chance of that happening. – Brendan Mulcahy
Added an edit with more information. Seems like this is a bug in SSIS – Brendan Mulcahy
The only thing that comes to my mind is to set MaxConcurrentExcecutables = 1. It might kill the performance though. – PacoDePaco
Or maybe first you should try the 'RetainSameConnection' = True for your ODBC provider. – PacoDePaco

1 Answers

-1
votes

enter image description here

Please find the statement timeout must give statement timeout in secs on odbc destination properties, default value 0 is there it means infinity in SSIS