Solved

ActiveX Script in a DTS package

Posted on 2009-04-08
1
526 Views
Last Modified: 2013-11-30
I have an ActiveX script in a dts package that combines two text files into one.  For some reason when that script is running it just goes into an infinite loop and instead of creating a file of 10 kB it creates a file of that is in large...almost in 20 GB and still does not stop.  I had to kill the session to make the package to stop appending.

Can someone please look over the script code that I have attached and let me know where the problem is?  This process has been running for more than a year and I never had an issue.
'**********************************************************************

'  Visual Basic ActiveX Script

'************************************************************************
 

Function Main()

DIM objFSO, objFolder, objTextStream

DIM Path , Datestring

DIM FileId , LineId, datestringLen

Fileid =1
 

Dim DestFile, Filedate, DestFileName
 

FileDate = DTSGlobalVariables("processDate").Value

DateString = DatePart("yyyy",FileDate) & Right("0" & _

DatePart("m",FileDate), 2) &  DatePart("d",FileDate)

Datestringlen = len(Datestring & ".csv") 
 
 

 DestFileName = "AriseReport9042_" & datestring & ".csv"
 
 

Path = "F:\AIGdatafeed\"

SET objFSO = CreateObject("scripting.Filesystemobject")

Set DestFile = objfso.CreateTextFile(Path & Destfilename,True)

SET objFolder = objfso.GetFolder(path) 

FOR EACH objFile in  objFolder.files 

	IF Right(objFile.name, Datestringlen) = Datestring & ".csv" THEN

		IF (Left(objfile.name, 15) = "AriseReport9042" ) THEN

		

			Set  objTextStream = objFSO.OpenTextFile(path & objFile.Name)
 

			IF fileid =1  THEN

			 	sfileContents = objTextStream.ReadAll

				Destfile.Write(sFileContents)  

			      

			ELSE

				Lineid =1

				While (objTextStream.AtEndOfStream <> True)

				sfileContents = objTextStream.Readline

				IF lineid <> 1 THEN

					Destfile.Writeline(sFileContents)  

				END IF

				Lineid =Lineid +1

				Wend 	

			

			END IF

			fileid = fileid +1

			Set objTextStream = nothing

			 IF (objFSO.fileexists ( path & "/complete/" & objfile.name)) THEN

				objFSO.DeleteFile  path & "/complete/" & objfile.name, TRUE

			END IF

			objFSO.movefile path & objfile.name, path & "/complete/" & objfile.name

		END IF

	END IF

NEXT
 
 
 

	Main = DTSTaskExecResult_Success

End Function

Open in new window

0
Comment
Question by:TejNit
1 Comment
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 250 total points
ID: 24103096
Can you see the appended files being moved to the complete folder? That would tell you whether there is an issue with the move part.
The logic on the script seems sound but there must be a problem somewhere. Do you know that that script will append as many files as it can find in the source as long as they match the AriseReport9042*.csv wildcard. So it won't just stop at 2 files.
If the MoveFile fails but does not throw an error (which I would not expect) then it would cause that behaviour.
Perhaps you temporarily could add a 'watchdog' which forces it to stop after 100 iterations. You would still get an incorrect output file but at least your process would stop eventually.
Can you open the output file and take a look? It should give you a clue as to the issue. i.e. is it the first file being repeated, the second file being repeated or just a line?
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now