April 7, 2008 at 7:42 am
Here's the simple situation. I have a Active X Script ; all it does it moves txt files from one directory to another and renames it with todays date. So, if I run the same package twice a day , it fails becoz it excepts "NO" File in the archive foledr with today day. All I need to add a time part on the file name so that its unique each time i run. The code is below----( I am very poor at Coding..) SO, any real help will be really Apperciateed....Function Main()
Dim Month, Day, Year, FileDate
Dim objFSO
Set objFSO=CreateObject("Scripting.FileSystemObject")
Month = DatePart("m", Now)
If Len(Month) = 1 Then
Month = "0"& Month
End If
Day = DatePart( "d", DateAdd( "d", -1 , Now) )
If Len(Day) = 1 Then
Day = "0"& Day
End If
Year = DatePart("yyyy", Now)
FileDate = Year & Month & Day
'MsgBox(FileDate)
If objFSO.FileExists("D:\BHISTORY\Prior\BHISTORY.txt") Then
objFSO.Movefile "D:\BHISTORY\Prior\BHISTORY.txt","D:\BHISTORY\Prior\Archive\" & "BHISTORY" & "_" & FileDate & ".txt"
End If
'Set objFSO = Nothing
If objFSO.FileExists("D:\BHISTORY\BHISTORY.txt") Then
objFSO.Movefile "D:\BHISTORY\BHISTORY.txt","D:\BHISTORY\Prior\BHISTORY.txt"
End If
Set objFSO = Nothing
Main = DTSTaskExecResult_Success
End Function
Kumar.
"A PROBLEM AIN'T A PROBLEM, IF IT CAN BE FIXED!!!"
April 8, 2008 at 12:12 pm
First, you probably want to avoid Active-X scripts in favor of the Script task (if you really need a script). Active-X is in SSIS as a compatibilityfeature for easing the conversion of old DTS packages, but is not a preferred tool for new development. That being said, I'm like you in not being comfortable coding (either variety), so used a SSIS File System task inside a ForEach loop to do pretty much the same thing you're attempting by renaming the file into the other folder with a timestamp inserted in the name.
The loop is set to return the name only (a radio button on the collection tab) into user variable @FilenameNoExt. We then used the optional variable propery "EvaluateAsExpression" to build the full incoming and outgoing filenames. I'll leave it to you to see how you could do this to first rename from your "Prior" folder down to the "Archive" folder, then rename from the higher level "BHistory" to "Prior" in two sequential ForEach steps.
Variables with literal values in them:
@PriorFolder=D:\BHISTORY\Prior\
@ArchiveFolder=D:\BHISTORY\Prior\Archive\
Variables evaluated as expresssions.
This first one gives you the date and time as a string in format "20080408_101707".
@ArchTime=(DT_STR, 4, 1252)DATEPART("yyyy", GetDate()) + RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mm", GetDate()), 2) + RIGHT("0" + (DT_STR, 2, 1252)DATEPART("dd", GetDate()), 2)+"_" + RIGHT("0" + (DT_STR, 2, 1252)DATEPART("hh", GetDate()), 2) + RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mi", GetDate()), 2) + RIGHT("0" + (DT_STR, 2, 1252)DATEPART("ss", GetDate()), 2)
Note double backslashes are needed:
@FileName=@[User::FilenameNoExt]+".txt"
@PriorFullName=@[User::BHistFolder]+"\\"+@[User::FileName]
@ArchiveRename=@[User::ArchiveFolder] +"\\"+ @[User::FilenameNoExt]+"_"+@[User::ArchTime] + ".txt"
Then, the FileSystem task inside your ForEach loop simply renames from User:: PriorFullName to User::ArchiveRename.
The second loop would work pretty much the same, with a source @BHistoryFullname and target @PriorRename (you get to build the expressions for those....).
April 9, 2008 at 1:51 pm
Hey john Sorry for late Thanks..
Actually i am doind DTS migration, so i am just trying to avoid any compexcity here. Another thing, I am very new to SSIS, so i am still in learning phase. I wish i could easily do what u mean to say in ur posting. I am trying, however. Let's see how things will go...
*** A problem AIN't A PROBLEM, if it cane be solved.***
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply