[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 587
  • Last Modified:

ssis package to name a file in the format yyyymmdd_filename.txt

I have the following expression
@[User::FileLocation]  + "\\" +
(DT_STR,4,1252) DatePart("yyyy",getdate()) +
Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) +
Right("0" + (DT_STR,4,1252) DAY( DATEADD("d",-1, GETDATE())  ),2) +  

I have set up an expression in my package, so that all files will follow the format

yyyymmdd_PatientUpdates.txt and the variable FileLocation contains the location where the file will be stored.

However, there is a problem with my logic, and was wondering if someone could help out here. I always need the value for yyyymmdd to be one day less than the current day. So if the day is today, then the format would be 20120904 and so on.

The problem occurs when I come to the first of the month, so the current day was Sept 1st, I would need the value to be 20120831, but it came out as 20120931. I can see why it happened that way, what I am not sure, is how I can make it come out the correct format each time.

I am not totally up to date on SSIS and expressions, so any help you can give would be appreciated, thanks.
1 Solution
JDEE8297Author Commented:
I think I just figured it out

"C:\\Temp\\ErrorCodes\\" + (DT_WSTR,4)YEAR(DATEADD("dd", -1, GETDATE())) + "-"
    + RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("dd", -1, GETDATE())), 2) + "-"
    + RIGHT("0" + (DT_WSTR,2)DAY(DATEADD("dd", -1, GETDATE())), 2) + ".txt"

with some modifications for my needs, this should do it, unless someone comes up with a better way of doing this. I will give this a go.

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now