June 18, 2008 at 4:15 pm
We have a DTS that is run from a job. The DTS loads data from an Excel spreadsheet into the database.
A previous developer has been running the job manually. I would prefer to automate it. I currently have a Activex script checking to see if the file is <= 60 minutes old. It works fine, but executing it from a job fails to log into the UNC path which requires Windows Auth. How do I access a path with a username password?
Function Main()
Dim oFSO, sFilename
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set sFilename = oFSO.GetFile("\\IPAddress\Folder1\subfolder\Testing.xls")
If DateDiff("n", sFilename.DateLastModified, Now) <= 60 Then
main = DTSTaskExecResult_Success
ELSE
main = DTSStepExecResult_Failure
End If
Set oFSO = Nothing
Set sFilename = Nothing
End Function
June 23, 2008 at 12:23 pm
I would use the WshNetwork object to map a network drive to the UNC path.
Function Main()
Dim objFSO
Dim WshNetwork
Dim strDriveLetter
Dim intCounter
Dim strLetters
Dim strAttemptedLetter
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set WshNetwork = CreateObject("Wscript.network")
strLetters = "efghijklmnopqrstuvwxyz"
intCounter = Len(strLetters)
Do While intCounter > 0
strAttemptedLetter = Mid(strLetters, intCounter, 1)
If Not objFSO.DriveExists(strAttemptedLetter) Then
strDriveLetter = strAttemptedLetter & ":"
Exit Do
End If
intCounter = intCounter - 1
Loop
WshNetwork.MapNetworkDrive strDriveLetter, "\\IPAddress\Folder1\subfolder", , "username", "password"
' add your code that checks the file's date/time created here
WshNetwork.RemoveNetworkDrive strDriveLetter, True
Set objFSO = Nothing
Set WshNetwork = Nothing
End Function
June 24, 2008 at 12:21 pm
Thanks. This is working as a temporary fix.
June 24, 2008 at 3:18 pm
Might seem counterintuitive, but you might build into your script the logic to implement the system command "net use" to set temporarily a drive letter, then use the drive letter in lieu of the UNC path. Net use has options to include username and password as switches.
net use b: \\ip\share\folder /user:username mypassword
After the file is done, use the /delete switch to remove the drive letter mapping.
net use /delete b:
I like b: because it is so rarely used.
Just a thought.
June 24, 2008 at 8:24 pm
Good idea...
I started looking into "net use" today. I was thinking it would be a good idea to put the mapping command into an encrypted bat file so the username and password are more secure. The delete can go into the DTS package since there aren't any security issues.
July 8, 2008 at 6:33 pm
Hi.
If you're trying to execute the DTS package from a job on a SQL2005 64-bit server, the problem you're probably running into is that there is a bug with the 64-bit DTS executable.
Instead of setting up a DTS package from within a job, setup a CMD line executable, and call the 32-bit DTS.exe to run the package. The command line exe will look something like this:
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe" /DTS "\msdb\dtsPackageName" /SERVER ServerName /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E
This example assumes your dts package is sitting in the msdb package store. If it is a file, then use the path and filename where it is sitting, of course.
Anyway, I was running a job which would run fine manually, but I could not execute from a job, and this was the problem with it. Hope that's of some help to you.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply