DTS produces no data when called from network server

I have created a VB program that executes a several DTS packages on our SQL Server.  This works fine when I run the program from my desktop (it takes about a minute to run) but when I run it on a network server, one of the DTS packages does not move any data from the source table to the target.  It appears to execute this DTS package (i.e., when I step through the code in VB the DTS.Execute step runs and no errors seem to occur), but instead of running for a minute or so, it returns instantly.

I am running the same program on my desktop and on the server, and the other configuration elements appear to be in sync.  I also ran the program from a colleague's desktop and it works fine there.  It seems as though the only difference is that I am calling the DTS from a network server instead of from my desktop.

This program was previously working correctly on the server.  The only thing that has changed is the IP address of the source data system (DB2 on AS/400).  I modified the connection in the DTS package accordingly.

Does anyone have any idea what's going on here?  I'm running out of stones to turn over.

Thanks!

- Walter

wlevyAsked:
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:
>>Does anyone have any idea what's going on here?<<
In a word:  Permissions.  The SQL Server Agent startup account does not have the appropriate permissions to the resources required in the DTS Package.
0

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
SireesCommented:
Chek if SQL Server agent has permissions
0
wlevyAuthor Commented:
I don't think it's permissions, because I can run the program (successfully executing the DTS) from other computers including my workstation.

Can you tell me specifically what to check in terms of permissions that could be different when I run the program from the netword server rather than from my workstation?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Anthony PerkinsCommented:
>>I don't think it's permissions, because I can run the program (successfully executing the DTS) from other computers including my workstation.<<
You are missing the point.  When you schedule the DTS Package using SQL Server Agent, you are no longer running the package, but rather it is running using the SQL Server Agent account.  That makes all the difference in the world.

>>Can you tell me specifically what to check in terms of permissions that could be different when I run the program from the netword server rather than from my workstation?<<
Again: Check the SQL Server Agent startup account.  If it is a "System Account", that's your problem.  If it is a specific account make sure it is:
A. A domain account and
B. Has access to the resources you are using in the DTS Package.
0
wlevyAuthor Commented:
I checked the SQL Server Agent (SQLSERVERAGENT) startup account in Services, and on the Log On tab the Local System Account radio button is selected.

This is as it has always been, it was not changed recently.  Once again, keep in mind that this application was working perfectly both on workstations and on a network server, until the IP address of the AS400 (from which DTS is supposed to retrieve data) was changed.  I modified the DNS on the workstations and on the network server to point to the new IP address.  Now it works when run on workstations, but when run on a network server the DTS package that retrieves data does not return any data.  Other DTS packages called by my VB program continue to work correctly.

> > "When you schedule the DTS Package using SQL Server Agent, you are no longer running the package, but rather it is running using the SQL Server Agent account.  That makes all the difference in the world."

Please excuse my ignorance, but what do you mean "schedule the DTS Package using SQL Server Agent?"  I execute the DTS packages from my VB program, like thus:

Call dtsExport.LoadFromSQLServer(strDTSServer, , , DTSSQLStgFlag_UseTrustedConnection, , , , strDTSMainPackageName)
dtsExport.Execute

...where strDTSServer and strDTSMainPackageName are variables holding the appropriate values.

I also tried a variation of the LoadFromSQLServer method where I specify a user and password corresponding to a login set up specifically for this project, with appropriate permissions to the database.  This didn't help.

0
SireesCommented:
>> but when run on a network server the DTS package that retrieves data does not return any data. <<

Is there a firewall blocking it ?

>.Other DTS packages called by my VB program continue to work correctly. <<

Do these also get data from the AS400 (with changed IP address)
0
wlevyAuthor Commented:
The only DTS that does not work correctly (i.e. does not retrieve data from the source) is the one that is supposed to get data from the AS400.  I have asked our network people if there is a firewall that might be blocking data transfers between the network server and the AS400; waiting for a reply.

I *can* ping the AS400 from the network server, but there still might be something blocking data transfers.

I wonder if there's some way to execute the DTS packages on our SQL Server box instead of the computer where the VB program that calls the DTS is running.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.