CopyFile in DTS ActiveX Script Task

2 Dec 2005 8:11 PM

atchleykl

I am trying to copy a file and giving it a name with a date behind it. Below
is my code I'm using in my DTS ActiveX Script Task. I'm getting a "File Not
Found" message on Line 25 (I marked it below). Can someone see the problem
with my code??
    Dim NYear
    Dim NMonth
    Dim NDate

    NYear = Year(Date)
    NMonth = Month(Date)
    NDate = CStr(NYear) + CStr(NMonth)

    Dim oFSO
    Dim sSourceFile
    Dim sDestinationFile

    Set oFSO = CreateObject("Scripting.FileSystemObject")

    sSourceFile =
"\\wstlfp02\ebs$\SQLDataAccess\WPE\PandGInvoicesToMCFA\PGInvoiceDetailYYYYMM"
    sDestinationFile =
"\\wstlfp02\ebs$\SQLDataAccess\WPE\PandGInvoicesToMCFA\PGInvoiceDetail" +
"_RunDate_" + NDate

    oFSO.CopyFile sSourceFile, sDestinationFile '***(This is Line 25)***

    ' Clean Up
    Set oFSO = Nothing

    Main = DTSTaskExecResult_Success

3 Dec 2005 9:26 PM

John Bell

Hi

I assume you have tried outputting the file names in a MsgBox and validated
that they are correct? http://www.sqldts.com/default.aspx?292 has an example
of using the filesystem object to copy a file, and
http://www.sqldts.com/default.aspx?200 has an example of using the date as
part of a filename.

Make sure that the account that you are running this has permissions to the
share, it may be worth getting it working with a local drive first.

John

Show quote

"atchleykl" wrote:

> I am trying to copy a file and giving it a name with a date behind it. Below
> is my code I'm using in my DTS ActiveX Script Task. I'm getting a "File Not
> Found" message on Line 25 (I marked it below). Can someone see the problem
> with my code??
>     Dim NYear
>     Dim NMonth
>     Dim NDate
>
>     NYear = Year(Date)
>     NMonth = Month(Date)
>     NDate = CStr(NYear) + CStr(NMonth)
>
>     Dim oFSO
>     Dim sSourceFile
>     Dim sDestinationFile
>
>     Set oFSO = CreateObject("Scripting.FileSystemObject")
>
>     sSourceFile =
> "\\wstlfp02\ebs$\SQLDataAccess\WPE\PandGInvoicesToMCFA\PGInvoiceDetailYYYYMM"
>     sDestinationFile =
> "\\wstlfp02\ebs$\SQLDataAccess\WPE\PandGInvoicesToMCFA\PGInvoiceDetail" +
> "_RunDate_" + NDate
>
>     oFSO.CopyFile sSourceFile, sDestinationFile '***(This is Line 25)***
>
>     ' Clean Up
>     Set oFSO = Nothing
>
>     Main = DTSTaskExecResult_Success
>