DTS to move folders

[Eyespi20]

I have a DTS job that reads through a set of files in a folder, extracts the data in the files into a table in my database.

Until I have everything working smoothly, I don't want to delete those files, but want to move them into another folder.

Can anyone point me somewhere that might have some code that would do this?

Thanks!

Margaret

 

[MDXer]

you can create an activeX Task that will do this.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[mutley1]

I'm sure the VB guys can give you a hint or 2 from the VB code side of things, but I tend to use xp_cmdshell as a SQL task in DTS as I am rubbish with VB and don't know it to write stuff:

exec master..xp_cmdshell 'move c:\folder\filename.txt c:\safe_folder\filename.txt'

or for ALL files in that dir:

exec master..xp_cmdshell 'move c:\folder\*.* c:\safe_folder\'

or any amalgamation you require - just as you would type the command prompt. One thing to remember is check permissions on the foldes for the account running it.

HTH,

M.

 

[Eyespi20]

Thanks -- I'm no good at VB either although I did find this code on the web, I can't get it to work -- it runs and doesn't give an error, but also doesn't move or rename the files.

Function Main()

	Dim oFSO
	Dim sSourceFile
	Dim sDestinationFile



	Set oFSO = CreateObject("Scripting.FileSystemObject")

	sSourceFile = "\\servername\foldername\subfoldername"
	sDestinationFile = "\\servername\foldername\subfoldername\"

	oFSO.CopyFile sSourceFile, sDestinationFile

	' Clean Up
	Set oFSO = Nothing

	Main = DTSTaskExecResult_Success
End Function

Keeps giving me an error at line 20 which is the destination folder. Since I copied and pasted the

 

[mutley1]

Margaret,

I'll have a look through some stuff the ex-employees have written in VB to see if I can find something that moves files that you may be able to adapt. My best guess is that the code you posted is copying the whole subfolder as opposed to files.

If anyone else is reading this post, is there any particular advantage with using VB as opposed to xp_cmdshell? I know xp_cmdshell is considered a risk in certain SQL Security tools unless restricted to sys admin but if it is restricted to sys admin and my domain account is sys admin, what is the advantage of coding as opposed to a simple command prompt line?

Thanks,

Matt.

 

[Eyespi20]

One of the things I'm having to deal with here and thats making me reluctant to use the cmdshell task is that these files all have different names and I have another piece that parses through them to get those names and extract the data.

Margaret

 

[mutley1]

Hi Margaret,

That's why I suggested in a previous post you can use either c:\xxxx\*.* or c:\xxxx\*.doc, or c:\xxxx\data.* or any other combination if it suits your needs. Unfortunately, I don't know your naming convention for incoming files that need to be copied. If you can give me a full example of incoming file names, what might want moving (e.g. are there any other files that are in the directory that should remain there) I might be able to help further. Just need a bit more on the full process.

Cheers,

M.

 

[Eyespi20]

They are all named filename_mmddyyy_hhmiss.csv

Margaret

 

[mutley1]

OK, so if you were to use xp_cmdshell, then make it the last step. The DTS would have parsed and imported, then as the final stage do the move to the safe directory:

1. Package parses for file names
2. Package extracts / imports from files in step 1
3. Now files are redundant and have had data extracted, move to safe directory.

Basically, you will not lose the files with a move, and if you use VB or cmdshell the effect will be the same - moving from 1 directory to another, or is there more to it?

If they are all filename_mmddyyy_hhmiss.csv, then just do a 'move c:\dir\filename* c:\safedir'

HTH,

M.

Rgds,

M.

 

[mutley1]

Don't know if this will work - as I say, got it out of an ex emloyee's package....

        DIM fso
	DIM sOldName, sNewName

	Set fso = CreateObject ("Scripting.FileSystemObject")
	sOldName = "C:\Program Files\BLAH"
	sNewName = "C:\Program Files\SAFE_BLAH"

	IF fso.FileExists (sNewName) THEN
		fso.DeleteFile(sNewName)
	END IF
	fso.MoveFile sOldName, sNewName