ActiveX Scripting and SSIS...

[akalinowski]

I have a web store that exports a file to the ftp that then gets picked up by my inventory system via FTP, my web store exports the order file every hour on the hour and my inventory system picks up the file every hour on the 15 minute... now it was all running fine and dandy until i had to do some maintenance on our inventory system, it was down for about 8 hours yesterday as i did hardware upgrades and the like... while my web store's SSIS packages exported the txt file and continuously overwrote them. after some google searching i added the ActiveX script to my SSIS package (below) and it works fine, but if the file does not exists (as it shouldn't) it reports an error, i made the SSIS package ignore the error and go to the next step, but now i got annoying warnings everywhere, my question is how do i rewrite the code to report success if it doesn't exist or if it does and renames it, only report error if it cant rename a file that does exist.

Function Main()
Main = DTSTaskExecResult_Success

Set fso = CreateObject("Scripting.FileSystemObject")

'
Set bFile = fso.GetFile("C:\Inetpub\ftproot\Product_Orders.txt")
aYear = year(now)
aMonth = month(now)
aDay = day(now)
aHour = hour(now)
aMinute = minute(now)
aSecond = second(now)
filename = "Product_Orders_" & aYear & "_" & aMonth & "_" & aDay & "_" & aHour & "_" & aMinute & "_" & aSecond & ".txt"
bFile.move ("C:\Inetpub\ftproot\"&filename)
'fso.MoveFile "C:\Inetpub\ftproot\Product_Orders.txt", "C:\Inetpub\ftproot\"&filename

Set fso = Nothing

End Function

digression
added bonus is that the guys who wrote the program that picks up the orders (they only do unix programming for my inventory system, or else i would employ them to do this as well) actually made it look for Product_Orders* so if it misses 8 hours worth of orders it grabs them all and imports them all the same.
/digression

THANKS GUYS!!!

akalinowski

MCSE 2000, A+, N+, LCP, CNE

 

[MDXer]

I don't use the ActiveX script task as it is there only for backwards compatibility. I would suggest you redo this process using a Script Task, the ActiveX task will disappear in a future release.

In a script task I would have a read/write variable wich fould flag the existence of the file. I would then have an expression on the control flow which only executed the next steps if the variable evaluated to true. This keeps the package from failing and allows a graceful exit when the file does not exist.

 

[tsuji]

Wrap the main functional line in a conditional statement.
[tt]
Function Main()
Main = DTSTaskExecResult_Success

Set fso = CreateObject("Scripting.FileSystemObject")

'
[blue]dim sfile
sfile="C:\Inetpub\ftproot\Product_Orders.txt"
if fso.fileexists(sfile) then[/blue]
Set bFile = fso.GetFile([blue]sfile[/blue])
aYear = year(now)
aMonth = month(now)
aDay = day(now)
aHour = hour(now)
aMinute = minute(now)
aSecond = second(now)
filename = "Product_Orders_" & aYear & "_" & aMonth & "_" & aDay & "_" & aHour & "_" & aMinute & "_" & aSecond & ".txt"
bFile.move ("C:\Inetpub\ftproot\"&filename)
'fso.MoveFile [blue]sfile[/blue], "C:\Inetpub\ftproot\"&filename
[blue]set bfile=nothing
end if[/blue]

Set fso = Nothing

End Function
[/tt]
(The sfile and the renamed file name's relationship should be tightened, but I'll leave it to you.)