01-28-06, 17:18 #1Registered User
- Join Date
- Nov 2004
- Helsinki, Finland
Unanswered: DTS connection problems with IBM DB2 UDB for iSeries OLE DB Provider
I'm trying to build a DTS package which would send data to AS400 after deleting existing data from AS400 table first. At the moment I can't make it because:
1) If I'm using "IBM DB2 UDB for iSeries OLE DB Provider" as a data source, I can't store the password in the connection which is really really strange?? Because the password isn't saved, the package can't be scheduled because someone needs to type in the password. Stupid.
2) If I'm using "iSeries Access ODBC Driver" I can't execute SQL DELETE over that connection. SELECT Works well but DELETE won't work. I'll get error message which says "HResult of 0x80040e14 (-2147217900) returned. Unexpected error occured. An error result was returned without an error message."
3) Transform Data Task fails when I'm using "iSeries Access ODBC Driver". Error message says that the number of failing rows exceeds the maximum. If I'll raise the max error number, the step executes, but 0 records are sent.
Somehow I get this feeling, that with "iSeries Access ODBC Driver" I cannot send, delete, update etc in AS400 and with "IBM DB2 UDB for iSeries OLE DB Provider" it could be done IF the password could be stored in connection.
Help anybody? How to store the password in OLE DB or how to delete & send data with ODBC connection.
01-30-06, 11:46 #2Registered User
- Join Date
- Dec 2002
Going from memory here; it's been a while.
1. Look at AS/400 driver provided by Hit Software.
2. Look at possible using windows authentication for the IBM server. You would have to have a domain user account as the service account for SQL Agent. This account would have to have a name with 10 or fewer characters and a password with 10 or fewer character. Create an account with the same name and password on the IBM server. Check the box for Windows NT authentication when creating the package.
3. I am using an old(er) driver provided by IBM called IBM 32-bit Client Access Provider. As I recall, it took a bit of finagling to get it set up properly, but it has worked like a charm for me in the past.
hmscottHave you hugged your backup today?