Post reply

variable value is wrong when package is run through job

  • January 11, 2008 at 4:39 am

    #154056

    I've got an SSIS package that copies the previous night's backup from a production server to a test server.

    I've got a file system task that copies a file, the name of which is defined by a user variable.

    If I execute the package from within Management Studio the task successfully copies the previous night's backup without fail. Let's say the file we're looking for was created on 1 Jan 2008

    I then import the package into the msdb database and create a job to run that package. The first time the job runs it successfully copies the previous night's backup. However, on subsequent runs it always copies the same file (i.e. the one created on 1 Jan 2008) rather than the previous night's file.

    If I delete the stored package and re-import it the job will run as expected the first time it runs but, again, on subsequent runs it will always try to copy that first file. It's almost as if the filename is hard coded into the package when I import it into msdb.

    Any ideas?

    Karl
    source control for SQL Server

  • January 11, 2008 at 6:49 am

    #767068

    How are you setting the value of your file name variable ? From an external package config or self contained within the package?

  • January 11, 2008 at 7:07 am

    #767079

    The variable is set from within the package. I just added a variable, set the property EvaluateAsExpression to True and filled in the expression. The Scope of the variable is set to the name of the Package (doesn't seem I can change that) and the namespace is User.

    Interestingly, I've just added an email task which fires on completion. This email sends an email detailing which file was copied. The body of the email is composed using the same variable and even though the email reports the correct file was copied this isn't actually the case.

    So maybe there's something wrong with the way I've set up the File System Task?

    The Operation property is set to: Copy File

    The IsSourcePathVariable property is set to: True

    The SourceVariable is set to: User::BackupFile

    Karl
    source control for SQL Server

Viewing 3 posts - 1 through 2 (of 2 total)

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