DTS Package not executing on some machines


We have one DTS package, and when we try to execute it, we get errors on some systems. Though the package used to be executing on those systems previously.

We are using windows authentication. All the users are in one AD group and that AD group is added as an user in the SQL server. So all the users have same level of access in SQL server. But suddenly we are getting issues while executing DTS package on few machines. It says - the DTS package encountered an error.

the DTS package uses some views and creates a flat file on one loaction. It then FTP that file to another location.

Thanks, Alok

more ▼

asked Sep 20, 2012 at 05:37 PM in Default

avatar image

20 1 1 1

Can you post the entire error message?

Sep 20, 2012 at 05:47 PM JohnM

What's changed?

Sep 20, 2012 at 05:52 PM ThomasRushton ♦♦

without some more details (e.g. some error messages) it'll be a bit difficult to give you an appropriate answer. As Thomas asked: where there any changes?

Sep 20, 2012 at 09:40 PM DirkHondong

The error message is - 1 tasks failed during execution.

There are two tasks total in the DTS package. 1. Transform data task 2. Activex Script task

The error comes in the first task only. First task queries the data from the database and creates a file on one location. I have checked I have full rights on that location. I can create/delete files there. I am connected with Windows Authentication.

There has been no changes on the machines apart from Auto Updates running on specific intervals.

Any help appreciated.

Thanks, Alok

Sep 21, 2012 at 04:01 PM alok
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Do I understand you correctly that this same DTS is being ran on multiple servers? If so is SQL Agent launching with the same user on each server? Does that Agent account have the needed permissions in each case? If you open the DTS package and step through each step does it work at that time and only error when executed through a schedule job?

more ▼

answered Sep 21, 2012 at 09:11 PM

avatar image

40.9k 39 94 168

Hello Tim,

I am sorry that my question was not clear.

The DTS package is on one SQL 2005 server only (under legacy). Our support team members some times need to run the DTS package as per the requirement. All the support team member's IDs are added in one Active Directory group. And that active directory group has added as an user in the SQL server. So all the support team members open the SQL server management console with the windows authentication.

The problem is - some of them can run the DTS successfully, whereas some of them not. Interesting thing is - the package used to be run on every user's login properly.

All the users system are getting updated through Auto Updates run by our IT team. It is frustrating as it is difficult for providing support to the application.

One option we are considering is to convert the DTS into SSIS package.

Thanks, Alok

Sep 22, 2012 at 02:33 PM alok

Does that active directory group have the proper rights to the folder where the flat file is being created? If the group was created after the fact, some users may have rights to the folder previously and can run it.

Sep 22, 2012 at 04:45 PM Tim
(comments are locked)
10|1200 characters needed characters left

Hello Tim,

Yes, all the users have full access on the folder where the file is getting created. We have checked it, and the users having issue, can create and delete files on that folder.

Thanks, Alok

more ▼

answered Sep 23, 2012 at 05:51 PM

avatar image

20 1 1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Sep 20, 2012 at 05:37 PM

Seen: 1477 times

Last Updated: Sep 23, 2012 at 05:51 PM

Copyright 2018 Redgate Software. Privacy Policy