Hello, I have a set of DFT in my SSIS package which extracts data from my source. I believe everytime a DFT is executed, it will open a new connection. And once the DFT completes in extracting the data, it closes the connection to the source implicitly. There is a limit on number of connections with my source server that can be open at a time. My new DFT is failing to acquire connection with the source for the reason that the limit of open connections has already exceeded. I am not sure whether the DFT has closed my connection. However this doesnot happen everytime and sometimes the ETL completes without any connection issues. Just to make sure that my ETL doesnot fail, 1. is it possible to close my OLEDB connection explicitly? 2. If I have to use a script component to force to the connection close, how is it possible to close an OLEDB connection open by the DFT? Can we get an handle to the open connection? 3. A workaround will be making a retry to connect before failing the package. But the only way to retry which I know is using a SQL Server Agent job. This, in my case is not possible as we are not using Jobs to schedule the package. Can we implement retry mechanism at the package level? Any help in any of the three points mentioned above is highly appreciated. However, please let us know if there is any other workaround/solution to this. Thanks in advance.

Try this, it may help: Cick on the Connection Manager in question, and in the Properties pane, look for one called RetainSameConnection. Set it to True.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Thanks for the quick reply. Apologies for missing a point here. The ETL doesnot have a single connection, with multiple DFTs using it in a single package. Rather, I have multiple packages, and each open a connection to the source and extracts data. I am not sure how to use the 'RetainSameConnection' property in this case. Please advise.

"RetainSameConnection" only really applies when you have multiple objects referencing the same Connection Manager inside a single package. I use it for coding Transaction support into my packages. I could never get the Transaction functionality to work ( that which was supplied natively in SSIS). Instead, I bracket my Data Flow with two Execute SQL Tasks, set to BEGIN TRAN and COMMIT TRAN, respectively. They both point to the same CM (along with the OLE DB Destination of the Data Flow), and that CM uses RetainSameConnection = True. In your case, I don't think it will help. I don't think there is a way to force multiple packages to all funnel through the same connection. When you say it has reached its limit, what limit is that? Theoretically, SQL should be able to handle thousands of simultaneous connections. Do you have it limited somewhere?Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

My suggestion is to run the no of packges which is equal to the number of connections allowed. i.e Say you have 10 packages and only 3 connections allowed.. You should implement a mechanism to make sure only 3 packages are running at the same time. For this you can start the 3 packages first and log the completion of the packages into a table.Once any one of package is complete you have to start the next package. I believe after the completion of package execution , the connection will be released.

The source has a limitation of accepting only 4 open connections at a time from one user to implement a fair usage policy. The source is an IBM DB2 server.

I have designed the ETL in a similar way. All tha packages are executed one after the other using the precedence constraint. Still I am facing this error. I tried to contact the concerned person at the Source system, they have suggested to retry the connection 1-5 times if it fails. Is there anyway that I can retry the connection at a package level?

First, I would say to the Source System Admins that SSIS should NOT be treated as a regular user. Get yourself a dedicated account and password that is not restricted by this policy. Consider this: The average user (connection) handles maybe a couple of kilobytes of data per use or per hour or whatever. But your SSIS 'user' will be handleing hundreds of mega-bytes of data. Different scenario, ergo, different rules. Give it a shot, what have you got to lose? If that doesn't work, maybe you could try spacing out the data flows by putting in a script task that pauses package execution by 10 or 15 seconds between each. The child packages SHOULD close out their connections, but sometimes it takes a bit for the system to figure out they are not needed. Can you run a package and monitor the latency between package end and Connection close time? Next, maybe CheckPoints would help. You would need to keep re-starting the package, though, and know when it is done so you DON'T try to re-start it.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.