Automating a DTS Package that accesses text files in a SharePoint Document library

I am working with SQL Server 2000. I have a DTS package that needs to acces text files in a SharePoint document library on another server. If I am logged on, I can run the DTS packages and the job will use my credentials to access the SharePoint library. The problem is, I need to set this up to automatically run the job on a daily basis. How can I get the DTS package to pass login credentials when the SQL Server Job agent invikes the job? Alternatively, is there another way that this can be handled so that I am not required to be logged on to allow access to these text files?
alm5586aAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
nmcdermaidConnect With a Mentor Commented:
>> How can I get the DTS package to pass login credentials when the SQL Server Job agent invikes the job

The DTS runs under whatever the SQL Agent service account is set to run under.

Go to the services applet and check what account SQL Server agent is running under. By default its Local\System, which has no network access.


Also I suggest that you do NOT right click and schedule your DTS as it is very difficult to work out what DTS you are running from that long string.


Instead just use this syntax in a job (as a CmdExec step type):

DTSRun /SLocalhost /E /N<name of package>


Lastly, if you cannot set your SQL Agent service to run under a useful windows login, you can use a CMD file to 'authenticate' to a folder on the fly:

NET USE \\ComputerName\FolderName <password> /USER:<UserName>

You need to put that into a CMD script which is in turn run by an execute process task in your DTS or by a CmdExec step in your job.

Of course you need to hard code a password in clear text so really this is a security issue.
0
 
ia2189Commented:
You can set the username and password of a DTS package from inside a SQL job using an operating system command.

dtsrun /S[server] /U[username] /P[password] /N[dtspackage]
0
 
TAB8Commented:
from the dts designer - schedule the package to run ..
this will create a job in the sql agent job section ..
look at the steps in the job that is created .. there will be a step to run the dts ..
eg dts run  skdhf9839831961984018740174102  ' lost of ms numbers :)'
 you can copy this cope into your own scheduled jobs .. and set your job to run under what ever account you want

0
All Courses

From novice to tech pro — start learning today.