Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
admin001
Posting Yak Master
166 Posts |
 Posted - 2003-10-30 : 04:28:43
|
| Hello ,I have two boxes, one dev and one Production, with SQL server 2000 Enterprise Edition . I have a DTS package on the dev. setup which was created for testing the data upload from a source text file residing on file server to one database on SQL server . Now due to security policy , we as system administrators , do not have access to those specific folders where the source file resides . Only the application owners or users are allowed to access those files . Now when I try to create the same DTS package on the Production SQL server , it displays an error as file not found . Obviously because I do not have access which as a result I cannot create the DTS for the daily data upload mechanism . The SQL server service and agent is running under a specific domain account . Do i have to add this service account in the group of users who already have access to folders and files so that it will be able to read the file and allow me to create the DTS or it has to be my admin account login . Need your advise and suggestions to resolve this ? I log in to the SQL server as my admin domain account which does not have access to some folders on the file server . The DTS steps involves first an Active X script which calls the source text file ( from a file server ) and pushes the data into the database with standard SQL login . Can you pl. help me to resolve this ?Thanks and Regards |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-10-30 : 05:40:40
|
| You say you got the error when you created the package. Is that right ?If not, how are you running the package when you get the error ?Damian |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-30 : 12:31:34
|
| When designing the package, the person who is designing the package needs their account to have permissions to the folder and/or files. When the package is scheduled, then the SQL account will need to have these permissions as well.Tara |
|
|
|
admin001
Posting Yak Master
166 Posts |
Posted - 2003-10-30 : 22:54:20
|
| Hello Merkin , Tara . Thanks for your reponse . What i did was just copied the package from the dev. SQL server to Production server . On the dev. server the package was created by the user who ha sgot access to the folder . I saved the DTS as structured file and copied it on Production server . Now I have already added the SQL server service account to the group where it has folder access . So if i schedule the DTS now as a job to run under the SQL account will the job run ? I haven't tried yet , since the server is not available at the moment due to some maintenance purpose . Will need some clarifications on this ?Thanks once again . |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-10-31 : 01:04:17
|
| It should work.It is usually a good idea not to give the sql server service account permissions on user access directories but to run a job (under control of the infrastructure dept rather than sql server) which copies the files onto the sql server machine or into a directory set up for the purpose. Then you won't have problems with having to change the sql server account permissions when you make changes to the application or need to access files from other apps.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
admin001
Posting Yak Master
166 Posts |
Posted - 2003-10-31 : 05:17:43
|
| Hello nr, Thanks for your suggestions .Finally my job failed even after running it under the SQL account . I checked the job history and the error was :Error string: The task reported failure on execution. I really don't know what it means , but seems like like probably I might have to start right from scratch for getting this DTS fixed . Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-31 : 11:29:43
|
| You should log into the server using the account that the service uses. Then trouble shoot the DTS package in the DTS designer. What I do is execute the individual steps one at a time to see where the problem is if the job history doesn't give enough information.Tara |
|
|
|
|
|
|
|
|
Cannot create DTS . Pl. help !!