December 8, 2008 at 6:08 pm
Hi all,
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 two steps (with a third, once I can get the first two working correctly) :
1. Execute SQL Task
2. ActiveX Script Task
3. Execute 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()))))
Can anyone provide advice for me on this? Otherwise any other suggestions would be greatly appreciated.
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
December 16, 2008 at 12:18 am
Please don't cross post. It just wastes peoples time and fragments replies.
No replies to this thread please. Direct replies to: http://www.sqlservercentral.com/Forums/Topic615879-19-1.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 16, 2008 at 2:59 am
Thank you GilaMonster for your advice. As I said in my other post, I only realised that the DTS Section existed at a later stage. Obviously posting in the DTS section, I would have had better luck getting a response.
Regardless of that, I didn't receive any replies and nobody wasted their time - apart from yourself in writing the above post.
In the future, I will try to ensure that I look at all of the forum categories before posting a query.
Regards,
James
December 16, 2008 at 3:24 am
james.ellingworth (12/16/2008)
Thank you GilaMonster for your advice. As I said in my other post, I only realised that the DTS Section existed at a later stage.
If you repost in a different forum, it's best if you reply to the original and put a link to the repost, so that someone running across the post days or weeks later can see that it's answered elsewhere.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply