Solved

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

Posted on 2007-04-04
5
544 Views
Last Modified: 2013-11-30
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?
0
Comment
Question by:alm5586a
5 Comments
 
LVL 3

Expert Comment

by:ia2189
ID: 18853967
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
 
LVL 10

Expert Comment

by:TAB8
ID: 18854561
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
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 250 total points
ID: 18855842
>> 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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question