DTS hangs when transfering data from AS/400

We have some DTS packages that hang when trying to transfer information from an AS/400. They work when executed from the developer's machine (i.e. Open Package and Execute Package), but hang when scheduled to run on the server. We have the package set to be single threaded (number of parallel tasks set to 1) and we set On Success Workflow to do the Transform Data tasks one after another.

We are using IBM's Client Access for ODBC to connect (Version 8.00.02 and a newer version, 8.00.47 I think, on one server). We have the problem with more than one AS/400 and on more than one SQL Server 2000. But we have other DTS packages that work fine.

Something must be misconfigured somewhere, but we can't find it. Any ideas?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
>>We are using IBM's Client Access for ODBC to connect<<
And they are installed on the server?

heltAuthor Commented:
The development server has the whole Client Access package and I believe is the same version as the developer's machines.
The new production server had just the Client Access ODBC Drivers installed and it has the newer version. Our infrastructure admin. is going to install the whole package to see if it makes any difference. The development server used to work, but doesn't anymore. We don't know what changed. It is actually my boss who wrote these DTS packages, so I'm working a bit second hand here.
Anthony PerkinsCommented:
Hopefully someone has some helpful ideas, but keep us posted.

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

How do you have DTS connecting to the AS400?  OLEDB for ODBC Drivers?  Linked server?
Log in as the SQL Server agent and run your DTS and see if it works.

When you schedule a package it runs as the SQL Server agent login. When you run it manually on the developer machine it uses whatever you've logged in as.
heltAuthor Commented:
In the DTS Package, Other(ODBC Data Source) is selected as the Data Source and the System DSN entered is for a Client Access ODBC Driver. The user and password are entered in DTS. I do know that DTS uses the DSN's on the machine it is ran on, but we have compared the DSN's and cannot find any differences.

We will try logging in as the account the SQL Server agent runs under and try running the package. I'll let you know the results.
heltAuthor Commented:
I don't know if we've really fixed the problem, but it appears the going back to an older version of the Client Access package seems to have fixed the problem. It also seems sensitive to the version of software on the AS/400. I think there a bug in the software, but since I'm dealing with the interaction of Microsoft and IBM, I think we are out of luck.
Ya, that is weird.  Usually, it was the older versions of Client Access that didn't like to do any kind of "non-ibm" communication and upgrading fixed it......Glad it's working though!

PAQed, with points refunded (500)

Community Support Moderator

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.