Help Using VBScript in an ActiveX task in DTS Package

Hi,

Thanks for reading.

I am creating a DTS package to import a .txt file into sql. I have everything in place, but the text file needs to have the last record deleted before the import. I need help with this part

I would like to delete the last record from a fixed width text file before I import it into sql. The number of rows will vary from file to file.

Can any one offer suggestions on the best way to do this.

I understand that I have to use the FSO to open and read the file, but I am not sure the best way to proceed after that.

Thanks in advance,
SteveThere are a couple easy ways to do this that I can think of:

1) Open the file up before import and delete the last record, then import to SQL Server.
2) Import to a temporary table that has a IDENTITY field in it, then delete the row with the highest value, then import to normal table.
3) If you want to delete the last line because it's an abnormal line (not a suitable record to go into the db), then just allow a certain number of errors. This way it'll basically error out without inserting the line.

The 1st solution needs the VBScript you're looking for. The problem I think with that is that the TextStream Object that you're looking for is a forward only object. This means that you'd have to open it, read each line at a time keeping track of which line you were on with some sort of local variable, then identify when you've reached the end of the file. Then you'd have to close the file, open it again, then read the file till you got to the last line (which you'd now know was the last line because of your local variable(s) that you initialized last time. Then you could delete that line. Here's the link for documentationhttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/jsobjtextstream.asp

The 2nd solution doesn't involve any VBScript, and would probably be simpler to explain and troubleshoot.

The 3rd solution was just a possible guess at what you're trying to do.

David