CODEWITHSUNDEEP
datessis
Kinchit Dalwani
Kinchit Dalwani

Reputation: 398

Adding month name to file in ssis

Is there any expression from which I can directly get the month name in expression builder?

I am supposed to add month name with file name dynamically. I am currently using "DATEPART" function from which i recieved the month number but I want Month name. Can anyone help me?

Upvotes: 2

Views: 6520

Answers (2)

BIDeveloper
BIDeveloper

Reputation: 2638

No - unfortunately not. You have two options:

  1. Return the month name from SQL as part of your dataset or
  2. Do a bit of a crazy expression:

(MONTH(yourDate) == 1 ? "January" : MONTH(yourDate) == 2 ? "February" : MONTH(yourDate) == 3 ? "March" : etc etc)

Upvotes: 3

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131180

SSIS Expressions have a very limited set of functions. If I remember correctly, it offers the functions that were available with VBScript way back when. There are no string formatting functions even in 2016 according to the documentation and people end up concatenating the various parts of a string.

You can use a Script step though to format a date using String.Format and store the result in a variable, as shown in [Using Variables in a Script Task] (https://msdn.microsoft.com/en-us/library/ms135941.aspx) and this SO question, eg:

var targetFolder=Dts.Variables["User::targetFolder"].Value;
var filePrefix=Dts.Variables["User::filePrefix"].Value;
var someDate=Dts.Variables["User::myDate"].Value;

var filePath= = String.Format(CultureInfo.InvariantCulture,"{0}_{1:MMMM}.txt",filePrefix,someDate);
var fullPath=Path.Combine(targetFolder,filePath);

Dts.Variables["User::filePath"].Value=fullPath;

The advantage of a script task is that you can use all of .NET's functions to format values (eg String.Format) and manipulate paths (eg Path.Combine, Path.GetFileNameWithoutExtension etc)

Upvotes: 0

Related Questions