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

Posted on 2012-09-05
Last Modified: 2012-09-05
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.
Question by:JDEE8297
    1 Comment

    Accepted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Suggested Solutions

    After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now