SSIS timestamp into a derived column

Question

SSIS timestamp into a derived column

cbrammer1219

SSCarpal Tunnel

Points: 4476
More actions
March 20, 2017 at 10:19 am

#319797

Good morning All,
So I have a small hiccup in my ssis package that I'm working on. I read a csv file and loop through each one, I have added a derived column to insert the timestamp at the time file was read, My problem is when it is reading the file it sometimes puts more than one timestamp on the file because of the size, and some it works correctly. Is there a way to delay the timestamp until the whole file is read then insert it into the derived column? I hope I explain this well enough to understand what I'm trying to accomplish. Thank You.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

TangoVictor SSChampion Points: 10975 More actions · Answer 1

If I'm understanding, you want a timestamp (date time value) in your data to correspond to when the file was processed. The issue is you are getting multiple values since some files take longer to process.

You could generate the timestamp in a script component and store this in a variable and use the variable in your derived column.
Also you could look at the system variables, maybe Container Start Time for the data flow task. If you are looping through the files and launching a data flow task for each that might work.

cbrammer1219 SSCarpal Tunnel Points: 4476 More actions · Answer 2

cbrammer1219

SSCarpal Tunnel

Points: 4476

March 20, 2017 at 1:10 pm

#1934079

Thank You, I will try this.

cbrammer1219 SSCarpal Tunnel Points: 4476 More actions · Answer 3

I haven't been able to get this to work, I get an error when using the variable method.

SSIS package "C:\Users\cbrammer\Documents\Visual Studio 2015\Projects\AWG_SalesData\AWG_SalesData\Package.dtsx" starting.

Error: 0xC001C012 at Foreach Loop Container: ForEach Variable Mapping number 2 to variable "User::filedate" cannot be applied.

Error: 0xC001C012 at Foreach Loop Container: ForEach Variable Mapping number -102185483 to variable "??????" cannot be applied.

SSIS package "C:\Users\cbrammer\Documents\Visual Studio 2015\Projects\AWG_SalesData\AWG_SalesData\Package.dtsx" finished: Failure.

JustMarie SSCertifiable Points: 7771 More actions · Answer 4

You don't need to script it. You can create a variable and set the expression to the System::StartTime variable. Then use your variable in the rest of the package.

TangoVictor SSChampion Points: 10975 More actions · Answer 5

Why are you trying to map a filedate variable in the foreach? What type of foreach are you doing? a FOREACH FILE Enum you only have the filename to map.
Also as JustMarie mentions you dont need to use the script task.
In your original post you said you added a derived column to do a timestamp, i'm going to assume it was a GETDATE statement. Just replace that with the System::StartTime variable.