asked on

Need to return a 2 digit month and day in SSIS expression builder

I need to return a leading zero when the month and or the day value is less than 10 when the SSIS expression builder code computes the new filename on a daily bassis. But the attached code does not do that and I have a very short period to fix this. I have looked at the on-line documentation, but the functions for SSIS expression builder are not clearly documented, or at least I am not seeing descriptions or examples. I would appreciate assistance

"\\" + "\\" + "KUCY2GENIDB01" + "\\" + "MarketingData" + "\\" +
(DT_WSTR,2) Month(GetDate()) +
(DT_WSTR,2) Day(GetDate()) + 
(DT_WSTR,4) Year(GetDate()) + 
"_CallData.csv" +

Open in new window

ASKER CERTIFIED SOLUTION

PedroCGD

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Steve Hogg

I use a substring function. See below.

SUBSTRING("0" +   (DT_STR, 2, 1252) DATEPART( "MM", GETDATE()) , LEN( "0" +   (DT_STR, 2, 1252) DATEPART( "MM", GETDATE()) ) - 1, 2 )

Open in new window

blberger

ASKER

I just needed to set the Date function in your solution to the GetDate() format and your solution did the trick that I needed. I appreciate the quick assistance.

PedroCGD

Ok..
here you have all the expression... only copy and paste into your Derived Column inside your Dataflow.

"\\" + "\\" + "KUCY2GENIDB01" + "\\" + "MarketingData" + "\\" + (DT_STR,4,1252)(DATEPART("yyyy",GETDATE())) + (LEN((DT_STR,2,1252)(DATEPART("MM",GETDATE()))) == 2 ? (DT_STR,2,1252)(DATEPART("MM",GETDATE())) : "0" + (DT_STR,2,1252)(DATEPART("MM",GETDATE()))) + (LEN((DT_STR,2,1252)(DATEPART("dd",GETDATE()))) == 2 ? (DT_STR,2,1252)(DATEPART("dd",GETDATE())) : "0" + (DT_STR,2,1252)(DATEPART("dd",GETDATE()))) + "_CallData.csv"
FormatDate-yyyyMMdd.JPG