January 23, 2016 at 9:22 am
I had a question about file name expressions with SSIS. We use expressions to build out file names so that we can dynamically name the according to the Date and Time (including seconds). Here is the expression I have used so far:
REPLACE(REPLACE(REPLACE(LEFT((DT_WSTR,200)(DT_DBTIMESTAMP)GETUTCDATE(), FINDSTRING((DT_WSTR,200)(DT_DBTIMESTAMP)GETUTCDATE(), ".", 1) - 1), "-", ""), ":", ""), " ", "")
This creates a string such as: 20160121140411 for the date 2016-01-21 14:04:11
In many instances I create the file in one task, and then later need to move or transfer the file in another task later in the process. If I refer to the variable that contains the expression it “recalculates/reevaluates” the file name and the process crashes because the seconds changed due to the processing time. So the file is created with ‘20160121140411’ but then when I try to move the file and reference the variable later in the package it recalculates it to be ‘20160121140415’ which it won’t find. What I have done to work around it is that I us a ForEach loop to loop through the directory where they are stored, but I was hoping there was a cleaner way to do it. I just can’t seem to make the variable not recalculate when I reference it later in the step.
Has anyone encountered this issue and found a work around? Do you know of any easier or cleaner way of handling this?
January 23, 2016 at 3:05 pm
A workaround is to use a Script Task to generate your "current datestamp" at the beginning of your Package and assign that to a Variable of type String. Then, for the duration of your Package execution your "current datestamp" variable will have the same value.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply