CreateObject("Scripting.FileSystemObject") not working in SQL 2K5

[MrJRW]

I have a DTS package that has an ActiveX task that uses the FileSystemObject to create a FTP batch file.

I'm now trying to run this package from SQL Server 2005.
Because of this and other tasks that won't migrate, I'm running this DTS package from a "Execute DTS Package Task" in SSIS.

The task that is failing is:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()

dim oFSO
dim oFile

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFile = oFSO.OpenTextFile(DTSGlobalVariables("TransferFTPCommands").Value,2,1)
oFile.writeline ("open " & DTSGlobalVariables("TransferFTPServer"))
oFile.writeline DTSGlobalVariables("TransferFTPLogin")
oFile.writeline DTSGlobalVariables("TransferFTPPassword")
oFile.writeline "ascii"
oFile.writeline "cd /"
oFile.writeline "cd Results"
oFile.writeline "put " & """" & DTSGlobalVariables("TransferFileName").Value & """"
oFile.writeline "bye"

oFile.Close
Set oFile = Nothing

Main = DTSTaskExecResult_Success
End Function

I'm getting a runtime error in line 10.
Any ideas way ?

 

[AlexCuse]

Do you have the DTS add in component for SQL 2005 installed?

Ignorance of certain subjects is a great part of wisdom

 

[MrJRW]

Yes,
The DTS package has been imported to the Management/Legacy/DTS listing;
From there I can open/edit/run the package;
I've been able to execute the indivual tasks. Only this one task remains not working.
JRW

 

[AlexCuse]

This is a dumb question, but does it work if you try oFSO.Open rather than OpenTextFile?

If FSO is not supported that will be very very bad for me when we make switch :-(

Ignorance of certain subjects is a great part of wisdom

 

[mrdenny]

Have you tried converting this into VB.NET code within an SSIS package?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[MrJRW]

Problem Solved !!

The problem actually lies in the 11th line:

Set oFile = oFSO.OpenTextFile(DTSGlobalVariables("TransferFTPCommands").Value,2,1)

Where the variable "TransferFTPCommands" is the path/filename of the text file the FileSystemObject is trying to create.

It turns out the drive where FileSystemObject was trying to write did NOT EXIST

Once I defined a "real" logical drive, the script worked fine.

O.K. to close
JRW