Need to add MMM-yyyy to end of file in exp.builder-SSIS

Question

Need to add MMM-yyyy to end of file in exp.builder-SSIS

BI_Developer

SSC Eights!

Points: 908
More actions
April 23, 2018 at 12:44 pm

#403291

Hello,
I've built a basic ETL ssis package that drops an Excel file from on network share to another network share. I've been asked to append the month and year to the file name as MMM-yyyy (i.e. Apr-2018). I've been able to write it as 04-2018 but haven't been able to figure out the code/syntax for it to show as Apr-2018. Below is the code I wrote, and please advise what I have to do for my solution to work.
"ReportName- " + Right("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + " " + (DT_WSTR,4)YEAR(GETDATE()) + ".xlsx"

Viewing 9 posts - 1 through 8 (of 8 total)

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

Phil Parkin SSC Guru Points: 246986 More actions · Answer 1

BI_Developer - Monday, April 23, 2018 12:44 PM
Hello,
I've built a basic ETL ssis package that drops an Excel file from on network share to another network share. I've been asked to append the month and year to the file name as MMM-yyyy (i.e. Apr-2018). I've been able to write it as 04-2018 but haven't been able to figure out the code/syntax for it to show as Apr-2018. Below is the code I wrote, and please advise what I have to do for my solution to work.
"ReportName- " + Right("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + " " + (DT_WSTR,4)YEAR(GETDATE()) + ".xlsx"

There is no elegant way of doing this, but it can be done longhand. Check this link for an example.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 2

I'll also state that you'll really, really end up hating yourself or someone will end up hating you if you use the format of MMM-yyyy for this because it's flat out not sortable in temporal order. You should use yyyy-mm instead. Trust me as one of those that has a deep and profound hatred for the folks that have made the mistake of MMM anything in every place I've ever worked at.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 3

Why are you doing this in SSIS at all, this can be done in one line in powershell as simply as

Move-Item "C:\Test\input.txt" ("C:\Test\input" + (Get-Date -UFormat %b-%Y) + ".txt")

And yes I would also recommend not using that date format, YYYY-MM works a lot better.

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 4

ZZartin - Monday, April 23, 2018 3:31 PM
Why are you doing this in SSIS at all, this can be done in one line in powershell as simply as
Move-Item "C:\Test\input.txt" ("C:\Test\input" + (Get-Date -UFormat %b-%Y) + ".txt")
And yes I would also recommend not using that date format, YYYY-MM works a lot better.

I don't know PowerShell worth a hoot but that UFormat string looks like it might be for the MMM-yyyy format. What's the format for YYYY-MM?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Burrows SSC Guru Points: 65143 More actions · Answer 5

Jeff Moden - Monday, April 23, 2018 4:53 PM
ZZartin - Monday, April 23, 2018 3:31 PM
Why are you doing this in SSIS at all, this can be done in one line in powershell as simply as
Move-Item "C:\Test\input.txt" ("C:\Test\input" + (Get-Date -UFormat %b-%Y) + ".txt")
And yes I would also recommend not using that date format, YYYY-MM works a lot better.
I don't know PowerShell worth a hoot but that UFormat string looks like it might be for the MMM-yyyy format. What's the format for YYYY-MM?

Get-Date -UFormat %Y-%m

Far away is close at hand in the images of elsewhere.
Anon.

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 6

David Burrows - Tuesday, April 24, 2018 5:56 AM
Jeff Moden - Monday, April 23, 2018 4:53 PM
ZZartin - Monday, April 23, 2018 3:31 PM
Why are you doing this in SSIS at all, this can be done in one line in powershell as simply as
Move-Item "C:\Test\input.txt" ("C:\Test\input" + (Get-Date -UFormat %b-%Y) + ".txt")
And yes I would also recommend not using that date format, YYYY-MM works a lot better.
I don't know PowerShell worth a hoot but that UFormat string looks like it might be for the MMM-yyyy format. What's the format for YYYY-MM?
Get-Date -UFormat %Y-%m

Thanks, David. Now the Op (and me) knows both ways. Let's hope the OP goes for the YYYY-MM format.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

BI_Developer SSC Eights! Points: 908 More actions · Answer 7

Phil Parkin - Monday, April 23, 2018 12:52 PM
BI_Developer - Monday, April 23, 2018 12:44 PM
Hello,
I've built a basic ETL ssis package that drops an Excel file from on network share to another network share. I've been asked to append the month and year to the file name as MMM-yyyy (i.e. Apr-2018). I've been able to write it as 04-2018 but haven't been able to figure out the code/syntax for it to show as Apr-2018. Below is the code I wrote, and please advise what I have to do for my solution to work.
"ReportName- " + Right("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + " " + (DT_WSTR,4)YEAR(GETDATE()) + ".xlsx"
There is no elegant way of doing this, but it can be done longhand. Check this link for an example.

Thanks for providing the link. I applied it only after I changed it to GETDATE() since ROWDATE doesn't work inside SSIS. But it was brought up to me that this ETL needs to run on the first fiscal Wednesday of every month, which means I have to figure out how to attach the correct fiscal month and year. We do have a date dimension view in our sql server which does have the fiscal month and year to the corresponding calendar date value, but this means I have to pass the month/year into a variable within SSIS. How could I accomplish this?

MattF SSCrazy Points: 2285 More actions · Answer 8

Basic SSIS post including how to map query parameters to variables: https://docs.microsoft.com/en-us/sql/integration-services/control-flow/execute-sql-task?view=sql-server-2017

MattF