Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

VB ScriptTask in SSIS Expand / Collapse
Author
Message
Posted Friday, September 14, 2012 8:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 10:26 AM
Points: 132, Visits: 338
I have no OOP languages under my belt. I've been given an SSIS package that a predecessor wrote that has some script tasks. Can somebody please help me with this really simple question?
She's renaming a file by appending today's date as part of the file name. I need to capture that "finished" filename to a variable. This is her code. I know how to declare the variable I want to hold the name within ssis, but how do I tell this script task to write that "DestPath" to my variable?

Public Sub Main()
Dim SourcePath As String = FileIO.FileSystem.CombinePath(Dts.Variables("strFilePathEditDropBox").Value(), Dts.Variables("strFileDropBox").Value())
Dim DestPath As String = FileIO.FileSystem.CombinePath(Dts.Variables("strFilePathEditDropBox").Value(), Format(Now(), "yyyyMMddhhmmss").ToString() & "_" & Dts.Variables("strFileDropBox").Value())

Try

FileIO.FileSystem.CopyFile(SourcePath, DestPath)
FileIO.FileSystem.DeleteFile(SourcePath)
Dts.TaskResult = ScriptResults.Success

Catch ex As Exception
Dts.TaskResult = ScriptResults.Failure
End Try


End Sub

End Class
Post #1359327
Posted Friday, September 14, 2012 9:05 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, September 18, 2014 1:52 PM
Points: 504, Visits: 1,870
how do I tell this script task to write that "DestPath" to my variable?

Three steps:

1. Set up a variable to hold the changed file name. For this example I'm going to call it NewName.
2. In the script task editor, list NewName in the ReadWriteVariables on the Script screen.
3. In the Script code, add a line that says:
Dts.Variables("NewName").Value = DestPath


FileIO.FileSystem.CopyFile(SourcePath, DestPath)
FileIO.FileSystem.DeleteFile(SourcePath)
Dts.Variables("NewName").Value = DestPath
Dts.TaskResult = ScriptResults.Success


That should take care of it.
Post #1359386
Posted Friday, September 14, 2012 9:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 10:26 AM
Points: 132, Visits: 338
That worked perfectly and was exactly what I needed.
Thanks so much!!
Post #1359468
Posted Friday, September 14, 2012 10:40 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, September 18, 2014 1:52 PM
Points: 504, Visits: 1,870
You're quite welcome. Happy to help.
Post #1359498
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse