0

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,

P. MAJ
  • 149
  • 9
  • I really like @billinkc's solution, but the way I solved this is to pass getdate() into a datetime parameter with SQL. It is calculated before the job starts so it stays static. – KeithL May 18 '21 at 18:46
  • Seems like you can set a variable once with a script task as well – KeithL May 18 '21 at 18:57
  • that is a very good advice, thanks :) – P. MAJ May 24 '21 at 11:22

2 Answers2

2

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.

billinkc
  • 59,250
  • 9
  • 102
  • 159
  • Many many thanks my friend :) helped me out alot! – P. MAJ May 18 '21 at 14:54
  • Sadly for me it seems the System::StartTime changes during running the package, it's very VERY weird. I have a variable StringStartDateTimeStamp which is an expression which converts System::StartTime to yyyymmdd_hhmiss. And I have it set as a caption so I can check it during running, but also in a filename for a backup (which is build through an expression which uses the variable) but the seconds are 4 seconds later. So for some reason StartTime does not work properly in all cases. – SuperDre Sep 08 '21 at 14:24
  • @SuperDre I have not experienced that. If you have a minimal reproduction, I'd be delighted to see it. The [documentation](https://learn.microsoft.com/en-us/sql/integration-services/system-variables?view=sql-server-ver15) calls out that it is the time the package started to run but there can always be corner cases. – billinkc Sep 15 '21 at 14:16
  • @billinkc Variable **StartDateTimeStamp** expr: `(DT_WSTR,4)YEAR(@[System::StartTime]) + RIGHT("0" + (DT_WSTR,2)MONTH(@[System::StartTime]), 2) + RIGHT("0" + (DT_WSTR,2)DAY(@[System::StartTime]), 2) + "_" + RIGHT("0" + (DT_WSTR,2)DATEPART("HH",@[System::StartTime]), 2) + RIGHT("0" + (DT_WSTR,2)DATEPART("mi",@[System::StartTime]), 2) + RIGHT("0" + (DT_WSTR,2)DATEPART("ss",@[System::StartTime]), 2)`. Caption of seqcontainer uses variable, also a variable for filename which is used in a query for backup. Seconds of caption has 4 sec diff (cap part of validating first? and filename execute?). – SuperDre Sep 17 '21 at 11:12
1

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}
Thom A
  • 88,727
  • 11
  • 45
  • 75