dts works but run same dts via sql job failed

hi all,
i posted this somewhere else but i think here is the right group.

i have a dts that shld write to a text file located at a mapped drive. i read somewhere that sql job does not recognise mapped drive, so i use UNC path in form \\128.1.1.1\dtsfile, which dtsfile folder is the shared folder name. i opened the folder permission to everyone.

thats abt the remote server. the sql server i am running is on windows NT, logon using Administrator to local. my sql is SQL 2000. i register the server using sa username. the server agent is start up using System account.

now the problem is when i run the dts interactively/manually, it runs succesfully. but when i run it thru SQL job it says "Access denied". Or full error is like this,

DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 Error Detail Records: Error: 5 (5); Provider Error: 5 (5) Error string: Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.

i really hope and appreciate if someone out there can help me out. thanks!

ps: pls let me know if more clues needed from my environment settings.The account that executes the scheduled job does not have the same permissions as you. You need the job to be run with an account with the necessary permissions (same as your perhaps) which also has a non-changing password.|||I had a similar problem which was caused by the account used by the sqlagent service. I changed it to a domain account with the appropriate privs, and the problem was resolved. Whey you exec the DTS job via the designer, it is using your credentials vs. the agent's credentials.|||thank u both for yr reply...

what do u mean by change it to domain account? i tried to start up the sql server agent using THIS ACCOUNT, which taking sql id that i created as sysadm and the name and password is the same username/password i log on to the server. the reason to do this to make the job using the same logon id as i run it thru dts designer. but now i am getting this error:

DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: Error opening datafile: Logon failure: unknown user name or bad password. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 Error Detail Records: Error: 1326 (52E); Provider Error: 1326 (52E) Error string: Error opening datafile: Logon failure: unknown user name or bad password. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.

i think my problem now, i am not sure when running the dts thru job, from where the job is taking username/password to connect to the remote server location. appreciate further help...thnks much.|||hi again...

i came across some suggestions on the net like this:
Make sure that the SQL Agent uses the same account as SQL Server service.

how to check the SQL Server service account?

really need yr expertise here..thanks.|||hi again...

i came across some suggestions on the net like this:
Make sure that the SQL Agent uses the same account as SQL Server service.

how to check the SQL Server service account?

really need yr expertise here..thanks.

start -> run -type 'services.msc'
Find the SQL Server and SQL Agent services, right click, properties, and go to the login tab.|||start -> run -type 'services.msc'
Find the SQL Server and SQL Agent services, right click, properties, and go to the login tab.

hi again...
i already changed the sql services to use logon username/password to be the same as sql agent. means both sql agent and sql services are using domain account now, no longer system account. but unfortunately, i am stil getting the same error...

anymore ideas can help me out here? please please...

tqvm.|||hi all again...
maybe i forgot to tell that my remote server i am connecting to is UNIX server (is that made a difference anyway?). well...just to test out, i just gave it a try to point to another remote server running on Windows. i shared the same folder and give sufficient privs while my sql server is stil using same domain account for sql agent as well as MSSQLSERVER services. then i run the job, it run successfully!

1.now i wld like to ask yr expertise here to guide me - how do we share a folder in UNIX to enable another server to connect to it? right now i just give permission read/write/execute to the folder. and it seems giving the above error - logon error/bad username or bad password.

2.how do we give permission to a server running on local? (not on network). the reason i asked is - normally its easy to give permission to some users on same network. is it by IP will work?

hope u all out there can help! thanks...really appreciate yr help.

dts to update table

Hi,
I am writing a dts package to update a sql table using data in a csv
file. I know that dts updates all mapped columns regard less of the column's
value has changed or not. I need to perform a special action when dts
actually changes any column's value but not when the column's value remains
unchanged. Where should I write a query to perform this action?
Thanks in advance.Hi
More information regarding how you are doing this may be useful. The
assumption is that you are loading the data into a staging table or using a
linked server. You also don't say what sort of action this is?
It might be that you want a trigger an checking the original values against
the updated ones, or you could find out which rows would be affected by
comparing the two tables before the update using the UPDLOCK hint to make
sure the data does not change until the end of your transaction.
John
"helpful sql" <nospam@.stopspam.com> wrote in message
news:us$aIcJDGHA.2644@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am writing a dts package to update a sql table using data in a csv
> file. I know that dts updates all mapped columns regard less of the
> column's value has changed or not. I need to perform a special action when
> dts actually changes any column's value but not when the column's value
> remains unchanged. Where should I write a query to perform this action?
> Thanks in advance.
>