Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2007-04-04
5
Medium Priority
?
550 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 1000 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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
A while back, I ran into a situation where I was trying to use the calculated columns feature in SharePoint 2013 to do some simple math using values in two lists. Between certain data types not being accessible, and also with trying to make a one to…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

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