Renaming FIle with ActiveX Script in SQL Enterpirse Manager

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Renaming FIle with ActiveX Script in SQL Enterpirse Manager

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-08-30 : 10:46:15

Tim writes "Hi,

First of all you have a great website it has been very helpful. I am working on a task that I thought would be pretty simple but I can't quite crack. I have designed an enterprise solution which runs an SQL Query task to populate a table every 10 minutes and transform the data to a text file. So far so good but now I need to write an active X script to change the filename to "filename_DDMMYYTIME" where the date time is read from the second line of the text file (first line is column names). I have some code (disclaimer - may be some artifacts from many failed attempts..) Thanks for your help! I am getting an error at the end of the for loop right now of "Expected Statement"

**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()
if Change_File_Name() Then
Main = DTSTaskExecResult_Success
else
Main = DTSTaskExecResult_Failure
end if
End Function

Function Change_File_Name()

dim fso
dim filesource
dimfTargetName
dim fSourceName
dim TextStream
dim Line
dim LineOut
dim StartReading
dim char
dim field
dim Path
dim PathSource
dim Flder
dim Fc
dim FileFound
dim FileExists
dim fName

FileFound=False

Path = "E:\WindLogics\"
fSourceName= "WindLogics_TurbineData.txt"

set fso=CreateObject("Scripting.FileSystemObject")
set fileSource= fso.Getfile(Path & fsourceName)

set Textstream = filesource.OpenAsTextStream(Forreading, TristateUseDefault)
startWriting=False

Line= TextStream.readline
Line=TextStream.readline

for i=0 to i=15
char=Mid(Line,i,1)
field= field+char
i=i+1
end for

Set TextStream=Nothing
fileSource.Close

fName="Horizon_BCTurbine_" & field & ".txt"

fso.CopyFile Path & fSourceName, Path & fName

end Function "

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-08-30 : 13:18:49

Tim,

Since your question is about VB and not SQL, please post in the "Other Development" forum. You'll stand a much better chance of getting help there.

Ken

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-08-30 : 14:22:13

quote:


Function Change_File_Name()

Ok... After looking at your code, I couldn't resist.

Here's a start. It's up to you to convert it to a function and set a return value - don't forget to change the Path, InFile and OutFile parts to point to your folders/files:


InFile = "Test.Txt"
Path = "C:\Temp\"

Set objFSO = CreateObject("Scripting.FileSystemObject")
set objFile = objFSO.OpenTextFile(Path & InFile, 1)

LineText = objFile.ReadLine
LineText = objFile.ReadLine

NewData = Mid(LineText, 1, 15)

OutFile = "Test_" & NewData & ".txt"

objFSO.CopyFile Path & InFile, Path & OutFile

Set objFile = Nothing
set objFSO = Nothing

This should get you started.

EDIT: Removed three lines of WScript.Echo and a WScript.Sleep I added while working this out.

Ken

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources