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
Solved

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

Posted on 2007-04-04
5
545 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Add a step to a system backup job 6 19
Row insertion failed. Array 5 48
MS SQL SERVER and ADODB.commands 8 23
SQL Recursion 6 19
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

861 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