December 8, 2008 at 8:01 pm
Hi all,
I posted this question in the Newbies section:
http://www.sqlservercentral.com/Forums/Topic615857-169-1.aspx
However, I wasn't aware that this forum existed, so I am transferring it over here as well!
I am relatively new to SQL Server and am attempting to design a DTS package which will organise a bunch of text documents on a file system whilst changing the path in the database which the application references them to.
Basically, users of the application generate letters for Employees and the application has a set path in which to save them into:
E:\applications\HOIST\BIS\drafts
What my intention is with this DTS package is to:
1. Create new folders in both drafts and finalised (if not already there), based on the employee's ID number stored in the database.
2. Move the associated files from the draftsfolders into the employee specific directory.
3. Update the database table which keeps track of the path for the file by adding the employee ID number into the path.
I initially intended to use the command "master.dbo.xp_cmdshell" to perform most of the file system manipulations, however I do not have that level of permission and the IT dept. will not allow me that access. Additionally, I would have to use cursors and through reading on this website, cursors do not seem to be very efficient (although once the initial transfer of files has completed, I expect that there would only be about 50 records per day that it would need to process - so performance isn't necessarily a problem).
So now I am using a DTS package with three steps:
1. Execute SQL Task
2. ActiveX Script Task
3. Possibly another SQL Task (but haven't got that far).
1. The Execute SQL Task looks like the following:
SELECT
Control.DraftLetterDirectory + '\' + StdLetterOut.FileName AS OLDDraftPath,
Control.DraftLetterDirectory + '\'+ CAST(StdLetterOut.EmployeeNo AS VARCHAR)+ '\' + StdLetterOut.FileName AS NEWDraftPath,
Control.DraftLetterDirectory + '\'+ CAST(StdLetterOut.EmployeeNo AS VARCHAR) AS CreateDraftDirectory,
StdLetterOut.FileName AS StdFileName
FROM StdLetterOut, Control
WHERE
(CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, StdLetterOut.DateCreated)))) >= (CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE()))))
AND AppName = 'BIS'
Now, the intention of this is to produce a result set from which (on successful completion of step 1) the VB Script in the ActiveX task will tick through and process.
2. The ActiveX Script is as follows:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
' Move File
Option Explicit
Function Main()
Dim oFSO
Dim sSourceFile
Dim sDestinationFile
Dim sNewDirectory
Dim sFile
Set oFSO = CreateObject("Scripting.FileSystemObject")
sFile = DTSGlobalVariables("StdFileName").Value
sNewDirectory = DTSGlobalVariables("CreateDraftDirectory").Value
IF NOT oFSO.FolderExists(sNewDirectory) THEN
oFSO.CreateFolder(sNewDirectory)
END IF
sSourceFile = DTSGlobalVariables("OLDDraftPath").Value
sDestinationFile = DTSGlobalVariables("NEWDraftPath").Value
MsgBox "OldDraftPath " & sSourceFile
MsgBox "NewDraftPath " & sDestinationFile
IF oFSO.FileExists(sFile) THEN
oFSO.MoveFile sSourceFile, sDestinationFile
END IF
' Clean Up
Set oFSO = Nothing
Set sSourceFile = Nothing
Set sDestinationFile = Nothing
Set sFile = Nothing
Set sNewDirectory = Nothing
DTSGlobalVariables("CreateDraftDirectory").Value = Nothing
DTSGlobalVariables("StdFileName").Value = Nothing
DTSGlobalVariables("OLDDraftPath").Value = Nothing
DTSGlobalVariables("NEWDraftPath").Value = Nothing
Main = DTSTaskExecResult_Success
End Function
I have attempted to use Global Variables to do this and has only processed the first line in the SQL code (i.e. created the first employee directory in the drafts directory and moved all the associated Employee files into the folder).
3. The third step (on success of step 2) would be another Execute SQL Script:
UPDATE StdLetterOut
SET StdLetterOut.FileName = '\'+CAST(StdLetterOut.EmployeeNo AS VARCHAR)+'\'+ StdLetterOut.FileName
WHERE (CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, StdLetterOut.DateCreated)))) >= (CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE()))))
Have been working on this one for a while, without any success. So if there is anyone out there with advice, it would be great to hear from you!
Cheers,
James
December 15, 2008 at 4:39 pm
I have had some assistance in working this one out (Thanks goes to Allan Mitchell). There were a couple of things going wrong with this.
Remember that this is in SQL Server 2000.
The first issue that I found is that there are two types of ActiveX Scripts. I thought that there was only the one type (Task ActiveX). The second is called a ActiveX Workflow Script, which can be found by right clicking on a task -> clicking the Workflow option and selecting "Workflow Properties" at the bottom of the menu. Then click on the Options Tab and at the bottom of this screen there is a checkbox with "Use ActiveX Script". It is here where I needed to initialize my global variables for further use within the Package.
The second issue is regarding the use of global variables within the ActiveX Task Script. This is what I needed help on the most. I was the global variables in the incorrect manner in my script. So instead of the following line:
IF NOT oFSO.FolderExists(sNewDirectory) THEN
oFSO.CreateFolder(sNewDirectory)
END IF
It should have been
IF NOT oFSO.FolderExists(sNewDirectory.Fields(0).Value) THEN
oFSO.CreateFolder(sNewDirectory.Fields(0).Value)
END IF
The same goes for the Move Files as well.
Additionally, it all needed to be stuck into a loop (after variable assignements):
DO UNTIL sNewDirectory.EOF OR sSourceFile.EOF OR sDestinationFile.EOF
IF NOT oFSO.FolderExists(sNewDirectory.Fields(0).Value) THEN
oFSO.CreateFolder(sNewDirectory.Fields(0).Value)
END IF
....
....
sNewDirectory.MoveNext
sSourceFile.MoveNext
sDestinationFile.MoveNext
LOOP
So I hope this helps if anyone is looking at approaching folder creations and file moves with a DTS package.
Cheers,
James
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply