Solved

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

Posted on 2007-04-04
5
546 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

730 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