SQLTeam.com | Weblogs | Forums

SSIS File Header Date 1 day out


I have a job on SSIS that runs every 1st of the Month and puts a CSV file into a folder that is then grabbed by another process, this process then checks the file name date is the same as the header date in the file. On change of the year the job failed and reported the file name date was different to the one in the file.

The file name date was the first of January 2021, and the date in the file was as it should be 01/01/2022.

I ran the job manually again and it was fine. Can anyone suggest why the automated run of the job, which happened on the 01/01/2022 at 07:30 (when I was off) created a file with the previous Year's first of January date?



Probably the expression that creates the date to be added to the file name is incorrect. But - without being able to see that code, it is impossible to determine.

The dynamic expression that creates the file name is as below:

@[User::DataDestServ] +"filenamepart1_"+(DT_STR,4,1252)YEAR( DATEADD( "dd", -1, getdate() )) + RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , getdate() ), 2) +"01_1.csv"

[User::DataDestServ] = the folder location for file

So when it ran on the 1st of Jan 2022 it created a file name with the Year as 2021, any ideas why it would do that?

Yes - this: (DT_STR,4,1252)YEAR( DATEADD( "dd", -1, getdate() ))

On 2022-01-01 - it gets the year of the previous day, which is 2021-12-31.


As soon as I looked at it, that bit started to cross my mind as the culprit.

I guess I'd fashioned this code up to give me the first day of the Month on the file name, which has been working fine until the Year change. Will need to rework it to stop that happening on Year change.