Solved

ActiveX Script in a DTS package

Posted on 2009-04-08
1
544 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

742 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