CODEWITHSUNDEEP
sql-servervariablesssisexpressionetl
Philip
Philip

Reputation: 2628

Expression Variable changing through run-time

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?

Upvotes: 2

Views: 65

Answers (2)

Yahfoufi
Yahfoufi

Reputation: 2544

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"

Upvotes: 1

billinkc
billinkc

Reputation: 61201

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

Upvotes: 1

Related Questions