2

I have a Variable that contains the following expression:

@[User::DestinationDirectory] 
+ "\\files\\Charges_"
+ RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , GETDATE()), 2)
+ "_"
+ RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , GETDATE()), 2)
+ "_"
+ (DT_STR, 4, 1252) DATEPART("yyyy" , GETDATE())


+ "_"
+ RIGHT("0" + (DT_STR, 2, 1252)DATEPART("hh", GETDATE()), 2) 
+ "_"
+ RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mi", GETDATE()), 2) 
+ "_"
+ RIGHT("0" + (DT_STR, 2, 1252)DATEPART("ss", GETDATE()), 2)
+ ".csv"

This variable is used as the file-name for a generated CSV within a Data flow:

enter image description here

Afterwards, I have the following within the Event Handlers/On Post Execute:

enter image description here

This checks if the file has had 0 rows and then deletes the file if it has.

enter image description here

The problem I'm facing is that the value of the variable changes from the time the file is created to the time it does the check to whether it needs to delete it or not.

How can I have the variable maintain its value at the start of the package and it not change?

Yahfoufi
  • 2,220
  • 1
  • 22
  • 41
Philip
  • 2,460
  • 4
  • 27
  • 52

2 Answers2

1

When the variable is an expression it is evaluated each time it is used, so GETDATE() function will return a different value each time you call the variable, using an Expression Task instead of that will evaluate the variable value once executed only.

So instead of Evaluating Variables as Expression in the variable properties, add an Expression Task before the Data Flow Task and use the following expression:

@[User::FilePath]  = @[User::DestinationDirectory]
+ "\\files\\Charges_"
+ RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , GETDATE()), 2)
+ "_"
+ RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , GETDATE()), 2)
+ "_"
+ (DT_STR, 4, 1252) DATEPART("yyyy" , GETDATE())
+ "_"
+ RIGHT("0" + (DT_STR, 2, 1252)DATEPART("hh", GETDATE()), 2) 
+ "_"
+ RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mi", GETDATE()), 2) 
+ "_"
+ RIGHT("0" + (DT_STR, 2, 1252)DATEPART("ss", GETDATE()), 2)
+ ".csv"

Update 1

Based on the comments, if you have 16 variables. In this case, you should add a variable of type Date, and assign its value using the Expression Task and use this variable instead of GETDATE() in the variables expression. example:

Expression Task

@[User::FileDate] = GETDATE()

Variable expression

@[User::FilePath]  = @[User::DestinationDirectory]
+ "\\files\\Charges_"
+ RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , @[User::FileDate]), 2)
+ "_"
+ RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , @[User::FileDate]), 2)
+ "_"
+ (DT_STR, 4, 1252) DATEPART("yyyy" , @[User::FileDate])
+ "_"
+ RIGHT("0" + (DT_STR, 2, 1252)DATEPART("hh", @[User::FileDate]), 2) 
+ "_"
+ RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mi", @[User::FileDate]), 2) 
+ "_"
+ RIGHT("0" + (DT_STR, 2, 1252)DATEPART("ss", @[User::FileDate]), 2)
+ ".csv"
Yahfoufi
  • 2,220
  • 1
  • 22
  • 41
  • This would require 16 Expressions Tasks, as I have this for 16 variables. Is there a cleaner way of doing it? – Philip Feb 24 '19 at 11:29
  • of course, add a datetime variable and use the expression task to assign its values at the beginning of the package, and use it instead of `GETDATE()` in the expressions – Yahfoufi Feb 24 '19 at 11:48
  • 1
    Thanks @Yahfoufi, that works a treat. Appreciate your assistance. – Philip Feb 24 '19 at 21:31
1

GETDATE() is evaluated every time it is accessed. Since the package does not complete within the name second, you get the drift throughout your package.

To pin a value for the entire runtime of the package, use the system scoped variable @[System::StartTime]

See also

billinkc
  • 59,250
  • 9
  • 102
  • 159