ActiveX Script to copy file 1

[itchyII]

Hello all, I am new to dts and I am trying to copy a file from one location on a server to another location on another server. I was told that I could do this using an Activex scripting task. I also want to append the date to the filename of the destination file. The problem with what I have, is that it doesn't work unless the destination file already exists. I want to create a new file for the destination (a copy of the source file), and then place it in a nother location with a different filename. Here is what I have so far.

Function Main()
    Dim fso, sourceFile, destFile

    Set fso = CreateObject("Scripting.FileSystemObject")
    sourceFile = "\\servername\foldername\filename.xls"
    destFile = "\\servername2\foldername2\filename2.xls"
    fso.CopyFile sourceFile,destFile,true

    Set fso = Nothing

    Main = DTSTaskExecResult_Success
End Function

Help please!

ItchyII

 

[cbhnp]

Looks like it should work.

Are you sure that you, or the user the DTS is executing as, has adequate permissions to create a file at the destination?

 

[itchyII]

Hi cbhnp, yes, the permissions are OK. It seems to work if I remove the filename of the destination file and just leave the foldername. It copies the file with the same name as the original file. Then I have to rename the file seperately.

Thanks anyways!

ItchyII

 

[EdwinGene]

Itchy,

You have more options than using only an ActiveX Script Task.

You can use the Execute SQL Task to execute the xp_cmdshell extended stored procedure.

1. Set up a DTS Global Variable to hold the xp_cmdshell command line. You could name it, SqlCommand, for example.

2. Use an ActiveX Script Task to set the global variable: DTSGlobalVariables("SqlCommand").Value = "Exec xp_cmdshell 'copy \\servername\foldername\filename.xls \\servername2\foldername2\filename2.xls'"

3. Use a DTS Dynamic Properties task to set the SQL Statement parameter of the Execute SQL Task to the SqlCommand global variable.

4. Execute the Execute SQL Task.


This approach would also open the door to reusing the DTS Package to copy other files. You could set up a SQL table to hold source and destination filenames and paths; then, using another Execute SQL Task, read the source and destination into DTS Global variables set up to hold them. Then, in step 2, above, use those source and destination global variables to build the SqlCommand global variable. Then, whenever you needed to copy a file you would just update the SQL table and run the DTS package.

 

[EdwinGene]

Itchy,

I had a brain hiccup.

In step 2 of my previous post the global variable value should read: "Exec master..xp_cmdshell 'copy \\servername\foldername\filename.xls \\servername2\foldername2\filename2.xls'"

Sorry about that.

 

[itchyII]

Thanks EdwinGene, this is really helpful!

Much appreciated!