process a file that has various extensions

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)

process a file that has various extensions

Author

Topic

Maquis
Starting Member

25 Posts

Posted - 2003-11-25 : 08:28:03

I have a DTS package that needs to process automatically-generated files, where the extension changes on a daily basis. The files that are generated are named "blah.101", "blah.167", "blah.97", etc. I was thinking of using an ActiveX script to first move the file to "blah.txt" and then import from there, but I can't figure how to find the file initially...help?
Thanks.

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-25 : 14:56:38

Can you do it in a sproc instead?


Insert Into Ledger_Folder exec master..xp_cmdshell 'Dir d:\Data\Tax\SmartStreamExtracts\*.*'

	SELECT @Result_Count = @@ROWCOUNT, @error_out = @@error

	If @Error_Out <> 0
		BEGIN
			Select @Error_Loc = 5
			Select @Error_Type = 50001
  			GOTO Load_Ledger_Init_sp_Error
		END

--	select * from ledger_folder

	Delete From Ledger_Folder_Parsed

	SELECT @Result_Count = @@ROWCOUNT, @error_out = @@error

	If @Error_Out <> 0
		BEGIN
			Select @Error_Loc = 6
			Select @Error_Type = 50001
  			GOTO Load_Ledger_Init_sp_Error
		END

	Insert Into Ledger_Folder_Parsed (Create_Time, File_Size, File_Name )
	Select	  Convert(datetime,Substring(dir_output,1,8)
		+ ' ' 
		+ (Substring(dir_output,11,5) 
		+ Case When Substring(dir_output,16,1) = 'a' Then ' AM' Else ' PM' End)) As Create_Time
		, Convert(Int,LTrim(RTrim(Replace(Substring(dir_output,17,22),',','')))) As File_Size 
		, Substring(dir_output,40,(Len(dir_output)-39)) As File_Name
	  From 	  Ledger_Folder
	 Where  Substring(dir_output,1,1) <> ' '
	   And (Substring(dir_output,1,1) <> ' ' 
	   And  Substring(dir_output,25,5) <> '<DIR>')

Brett

8-)

nr
SQLTeam MVY

12543 Posts

Posted - 2003-11-25 : 15:15:53

Do you have to use dts?
This will import and archive all files that arrive in a directory using bcp
http://www.nigelrivett.net/ImportTextFiles.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Maquis
Starting Member

25 Posts

Posted - 2003-11-26 : 09:28:59

Yes, I had other steps to process from there, so this is how I figured out to do it...thanks for the help!

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
' Move File
Option Explicit

Function Main()

Dim fso
Dim sSourceFile
Dim sDestinationFile
Dim fsoFolder
Dim fsoFile

Set fso = CreateObject("Scripting.FileSystemObject")
Set fsoFolder = fso.GetFolder("\\server\ACD Reports")

For Each fsoFile in fsoFolder.Files
' Get filename
if left( fsoFile.Name, 9) = "overnight" then
sSourceFile = "\\server\ACD Reports\" & fsoFile.Name
exit for
end if
Next

sDestinationFile = "\\server\ACD Reports\overnight.txt"

fso.MoveFile sSourceFile, sDestinationFile

' Clean Up
Set fso = Nothing
Set fsoFolder = Nothing
Set fsoFile = Nothing

Main = DTSTaskExecResult_Success
End Function

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources