timestamp to exception file

  • I am writing source and destination exception files. However, the exception files get overwritten each time the package is run. I have to save each exception file for later audits.

    How can this be achieved? Can i add a timestamp to the files, if yes, How?

    Thanks!

  • The simplest way is to have another step that renames the file after each run. Something like a batch file.

    To do it within DTS:

    Create a global variable

    Create a Dynamic Properties Task

    Create an activex script to rename the global variable to the name fo the file that you want.

    Link the global variable to the file using the Dynamic Properties task.

    See http://www.sqldts.com. There are examples to show you exactly how to achieve.


    ------------------------------
    The Users are always right - when I'm not wrong!

  • The way I prefer to do this is to add a sql task with the following query:

    declare @day varchar(2), @month varchar(2), @year char(4), @filedate char(8), @cmd sysname

    set @day = datepart(dd, getdate())

    set @month = datepart(mm, getdate())

    set @year = datepart(yyyy, getdate())

    set @filedate = @year + '' + right('00' + @month, 2) + right('00' + @day , 2)

    set @cmd = 'move c:\folder\filename.txt ' + 'c:\folder\folderarchive\filename_' + @filedate +'.txt'

    begin

    exec master..xp_cmdshell @cmd, no_output

    end

    Just another option...  have a great holiday!

  • Add an active-X script to change the ExceptionFileName property of your Data Pump Task Object before it is executed.  Remember to include the full path to the filename.

    -Mike Gercevich

     

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply