locked
Keeping Dynamic DateTime Variable "Static" RRS feed

  • Question

  • In one step of an SSIS package, i create an outgoing XLS filename based on the current datetime setting, a la:

     

    "myFileName_" + (DT_WSTR,4)YEAR( getdate()) + RIGHT("0" + (DT_WSTR,2)MONTH( getdate()),2)+
    RIGHT("0" + (DT_WSTR,2)DAY( getdate()),2) +
    RIGHT("0" + (DT_WSTR,2)DATEPART("hh", getdate()),2) +
    RIGHT("0" + (DT_WSTR,2)DATEPART("mi", getdate()),2) +
    RIGHT("0" + (DT_WSTR,2)DATEPART("ss", getdate()),2) +".xls"

     

    which provides the format as myFileName_yyyymmddhhmmss.xls.

     

    This value is then assigned to a variable, user::myFilenameDateTime.

     

    This variable is referred to in various steps which need the full pathname or filename.

     

    I found, though, that in subsequent steps, the value for user::myFilenameDateTime is re-calculated whenever the variable is invoked.

     

    So in one Task i created the physical output XLS file and named it "correctly", eg, myFileName_20070428090204.xls; in the next Task, i call a Stored Procedure in SQL Server 2005 to email the file with a corresponding message (that pulls in more data from the database).

     

    The single Parameter to the Stored Procedure (that does the emailing) is the supposed/expected full Pathname of the outgoing file just produced but the parameter no longer represents the original filename -- and has changed slightly (a few seconds have been added) and the filename is now, myFileName_20070428090210.xls; consequently, my Email Distribution program cannot find the file with that specific name, although File = myFilename_20070428090204.xls does certainly exist.

     

    so it appears that these variables are calculated realtime whenever encountered.

     

    1) Am I misunderstanding something or misusing the variable assignment?

    2) How can i keep << myFileName_yyyymmddhhmmss.xls >>, "static" throughout the duration of the overal SSIS Process.

    3) I would think that even if I assign the derived myFileName value initially to ANOTHER "static" variable, this won't achieve anything because the new variable will be re-calculated again, as well, when it is invoked.

     

    thx/spirits,

     

    seth j hersh

     

     

     

    Saturday, April 28, 2007 2:20 PM

All replies

  • Use one of the few System:: date variables instead of getdate().
    Saturday, April 28, 2007 2:35 PM
  • I used the Package StartTime but that didn't help; ie, the datetime string continued to change.

     

    i created an USP (that OUTPUTted a datetime string) in SQL Server, pulled it back to SSIS and used this one-time calculation successfully.

     

    thanks for your help, Phil.

     

    Seth J Hersh

    Thursday, May 10, 2007 6:44 PM
  • I haven't tested this, but it seems like if you used a script task to copy the value of your expression based variable to a "static" variable, that the static variable would remain consistent for the rest of the package.

     

    Just another potential solution.

    Thursday, May 10, 2007 7:04 PM
  • Hello, 

    I had this same issue, if you use an expression task to set a variable with the timestamp data needed (using an expression), and then put it all into a container, then it will work. The lag in seconds is no longer an issue since the expression is only calculated one at the beginning, because it is in a container. Without being inside of a sequence container or another container, the expression will re-evaluate every time it is called within the package.

    Hope this helps!

    Monday, August 24, 2020 8:13 PM
  • hi

    The bellow code may  help you

    SELECT 'myFileName_' + FORMAT ( GETDATE(), 'yyyyMMddHHmmss') + '.xls'

    Thanks and regards

    Tuesday, August 25, 2020 9:19 AM