CODEWITHSUNDEEP
sql-servervisual-studiossis
P. MAJ
P. MAJ

Reputation: 159

How to keep SSIS variable static during the package execution

I have a SSIS package which reads from the MSSQL database and saves it into a xlsx file.

I dynamically change the extract with the following format: [filename - ddmmyyyy hhmm].xlsx

problems is this: my SSIS package creates a file like [filename - 18052021 1400].xlsx

however when it tries to email it for example, the time is now 1401 and it tries to find a file name like [filename - 18052021 1401].xlsx which does not exist and so it generates an error.

Is there any way to keep the variable static through the execution?

Many thanks,

Upvotes: 0

Views: 1192

Answers (2)

billinkc
billinkc

Reputation: 61201

The problem you're experiencing is that every time a variable with an Expression is read, it is evaluated. I vent about this from time to time in my answers because it can be an insidious little problem to track down.

Currently, you are building a file name something like

"FileName - " + (DT_WSTR, 2) day(getdate()) ...

The problem as I've already hinted and you're experiencing is that every time that expression is evaluated, SSIS checks the current time. If your package runs for more than a minute, you'll have crossed the boundary and now have a "new" name to deal with.

The way to resolve it, is to use a System scoped variable, @[System::StartTime], instead of the getdate

"FileName - " + (DT_WSTR, 2) day(@[System::StartTime])) ...

StartTime is the time the package itself starts. It could run for a minute or a day and the value will remain constant because it's what you expect - the time the package started.

If you need something that can change but remains constant for a specific scope, put all the pieces in a Sequence Container and then you can make use of a System scoped variable named (approximately) ContainerStartTime. The container (sequence, foreach, for) only has one start time but it can be 10 minutes later than the package itself started.

Upvotes: 2

Thom A
Thom A

Reputation: 95544

Presumably your variable's value is an expression then, not a Value? If so, then don't use an Expression for the variable, assign it a value, and then assign a new value of the variable at the start of your SSIS package using a expression Task. Without the variable name, nor your expression, I can't give the exact solution, but the expression task would have an expression like:

@[User::YourVariableName] = {Your original Expression}

Upvotes: 1

Related Questions