January 25, 2010 at 3:11 am
Hi All,
I am using Microsoft DB2 OLE DB provider in SSIS (2008) to select data (by SQL Task) from DB2 database. Then use the Script Task to write the result set in text file (having column values as tab separated). Now the issue is as follows:
1.) If I execute the package from command prompt then after generating around 1900 files, package fails with following error:
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.
Error code: 0x80040E14.An OLE DB record is available. Source: "Microsoft DB2 OLE DB Provider"
Hresult: 0x80040E14
Description: "A TCPIP socket error has occured (10048): Only one usage of each socket address (protocol/network address/port) is normally permitted.".
2.) If I execute the package from Visual Studio then after generating around 28K files, an error pops up saying need to close Visual Studio.
Any suggestions on this would be highly appreciable.
January 26, 2010 at 3:27 am
Are you saying that when you run from a command prompt the package works correctly about 1900 times and you get the expected data from DB2 then it stops working? And if you run interactively in VS it works OK about 20K times then stops working?
Or are you saying it does not work at all, or fails to get data from DB2?
If you are getting data from DB2 and the package stops working after some time, the most likely cause is a memory leak. I suggest you look at your code to check you are properly cleaning up all objects you open, as the most likely reason is your code is leaking memory. You can also use a tool such as Process Monitor to try to find where the memory leak is happening.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
January 26, 2010 at 4:16 pm
I do not have SQL2008 - importing from DB2 with SQL2005 using the IBM-supplied drivers installed via "Client Access" (slow OLEDB, fast ODBC via DTS2000 Legacy!). I am not sure which MS drivers you could use.... unless you have the SQL Enterprise edition afaik you do not get MS drivers for DB2..
Just in case, check and install your IBM "Client Access" drivers. The version we are using is as below.
It may be beneficial to keep up with all service packs of course if your iSeries is also updated.
-------------
Readme File for System i Access for Windows Service Packs
5761-XE1 V6R1M0 Cumulative Service Pack Level
-------------
SI34289
-------------
(c) Copyright IBM Corporation 2009. All rights reserved.
February 4, 2010 at 12:10 am
I checked for the memory leak, but there is no problem in the code.
February 4, 2010 at 12:12 am
Initially i used the Microsoft OLE DB provider for DB2. Then I changed to driver provided by IBM. Its working fine now. Its generation 60K files without any issue.
Thanks a lot for your suggestions.
February 4, 2010 at 1:43 am
amritgupta_ssitm-1059984 (2/4/2010)
Initially i used the Microsoft OLE DB provider for DB2. Then I changed to driver provided by IBM. Its working fine now. Its generation 60K files without any issue.Thanks a lot for your suggestions.
Glad to hear you've overcome the problem.
60,000 files? That is a lot indeed.
Sad to hear the MS DB2 drivers (were they OLEDB? other?) seem to have a memory leak or something.
This should be a matter for MS Connect as the MS drivers only install on the pricey Enterprise edition of MSSQL.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply