March 4, 2014 at 10:36 am
Hi all,
Could I get some advice how to rewrite (or retask) the following. I am re-creating a DTS package in SSIS 2K8, the first script does the following:
Function Main()
Dim oFSO
Dim sSourceFile
Set oFSO = CreateObject("Scripting.FileSystemObject")
sSourceFile = "D:\SQLExport\UVMS\FGAClosedFile.txt"
' Check if file exists to prevent error
If oFSO.FileExists(sSourceFile) Then
oFSO.DeleteFile sSourceFile
End If
' Clean Up
Set oFSO = Nothing
Main = DTSTaskExecResult_Success
End Function
At the end of the the stream, another script that appends the date to the filename (I think!!!)
sFileNameNew = "D:\SQLExport\UVSM\Leaseproject\FGANZacClosedFile" & YEAR(Now()) & RIGHT("00" & MONTH(Now()),2) & RIGHT("00" & DAY(NOW()),2) & ".txt"
sFileFAFS = "\\fafs-files.Kilverstone.local\Fleet Managed Report\FGAClosedFile" & YEAR(Now()) & RIGHT("00" & MONTH(Now()),2) & RIGHT("00" & DAY(NOW()),2) & ".txt"
Set oFSO = CreateObject("Scripting.FileSystemObject")
If oFSO.FileExists(sFilename)
Then
oFSO.CopyFile sFilename , sFileNameNew
oFSO.CopyFile sFilename , sFileFAFS
Set oFSO = Nothing
Main = DTSTaskExecResult_Success
Else
Set oFSO = Nothing
Main = DTSTaskExecResult_Failure
End If
End Function
Would appreciate knowing what the second script is doing, whether this should all be scripted, and how.....
Cheers,
JS
March 4, 2014 at 1:46 pm
I think you can replace both scripts with the File System Task in SSIS. The first one would be a delete task and it won't do a delete if the file doesn't exist and it doesn't return an error.
The second one looks like it is essentially archiving an existing file off to 2 new files. In this case you'd need to File System Tasks, each doing a copy file. You can use an expression within a variable to generate the destination file names. So you'd have 3 file connections, 1 for the source file, and 1 for each destination file. Then I'd have a variable called FGANZacClosedFile and one called FGAClosedFile that would have expressions that create the file name including the date. Then you'd use these 2 variables as the destination for the 2 file system tasks that are doing copy file. You would just have to handle errors if the file doesn't exist
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 5, 2014 at 3:53 am
Jack Corbett (3/4/2014)
I think you can replace both scripts with the File System Task in SSIS. The first one would be a delete task and it won't do a delete if the file doesn't exist and it doesn't return an error.The second one looks like it is essentially archiving an existing file off to 2 new files. In this case you'd need to File System Tasks, each doing a copy file. You can use an expression within a variable to generate the destination file names. So you'd have 3 file connections, 1 for the source file, and 1 for each destination file. Then I'd have a variable called FGANZacClosedFile and one called FGAClosedFile that would have expressions that create the file name including the date. Then you'd use these 2 variables as the destination for the 2 file system tasks that are doing copy file. You would just have to handle errors if the file doesn't exist
Hi Jack,
What I found when I've ported the delete script into a Script Task component, is it seems to have worked, but are there any downsides?
March 7, 2014 at 2:04 pm
Jake,
Sorry for the delayed reply, but I was originally going to put together a demo of what I'm talking about, but haven't had the opportunity to do it.
I'm just guessing here, but I think you might find some better performance using the built-in components and if you ever upgrade it will probably be easier.
I haven't really worked with migrating DTS scripts to SSIS, so I can't really offer anything else. If it works great.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply