I'm in the process of reworking some SSIS packages I inherited. A lot of these use Execute SQL Statement components to update certain fields in the tables once they've been loaded. I'd like to replace these individual UPDATE statements with Derived Column components.

I've got 95% of them converted, except for a couple, one of which has me a bit perplexed and was hoping someone could provide some insight.

Essentially, there is a column called POSTPERIOD which is a date in string format YYYYMM (i.e., 201503). The SQL update parses out the month piece and converts it to the month name and populates a column called POSTPERIOD_MONTH.

Here is the SQL:

select    DateName(month , DateAdd(month, CONVERT(INT, SUBSTRING(POST_PERIOD,5,2)), 0 ) - 1 )

I'd like to accomplish this using a Derived Column, but not sure how to go about doing this...

If someone could point me in the right direction, it would be greatly appreciated!

Thanks!

Unfortunately SSIS doesnt have a strighforward function to get you the month name from a date 

So inside derived column you need to use an expression like this

(DT_WSTR,30) ((DT_I4)SUBSTRING([POST_PERIOD],5,2) == 1? (DT_WSTR,30) "January" : ((DT_I4)SUBSTRING([POST_PERIOD],5,2) == 2 ? (DT_WSTR,30) "February" : ((DT_I4)SUBSTRING([POST_PERIOD],5,2) == 3 ? (DT_WSTR,30) "March" :((DT_I4)SUBSTRING([POST_PERIOD],5,2) == 4 ? (DT_WSTR,30) "April":((DT_I4)SUBSTRING([POST_PERIOD],5,2) == 5 ? (DT_WSTR,30) "May" : ((DT_I4)SUBSTRING([POST_PERIOD],5,2) == 6 ? (DT_WSTR,30) "June" : ((DT_I4)SUBSTRING([POST_PERIOD],5,2) == 7 ? (DT_WSTR,30) "July" : ((DT_I4)SUBSTRING([POST_PERIOD],5,2) == 8 ? (DT_WSTR,30) "August":((DT_I4)SUBSTRING([POST_PERIOD],5,2) == 9 ? (DT_WSTR,30) "September":((DT_I4)SUBSTRING([POST_PERIOD],5,2) == 10? (DT_WSTR,30) "October": ((DT_I4)SUBSTRING([POST_PERIOD],5,2) == 11 ? (DT_WSTR,30) "November" : (DT_WSTR,30)"December")))))))))))

Explained here also

http://www.dwknight.com/architecture-modelling/ssis/etl/extract-month-day-name-date-column-ssis-package/

Wow...!

Never would have thought of that! Thanks!

I'll give it a try!!

Visakh:

Thank you for the reply! I've tried your solution but am getting the following error(s):

Warning 17 Validation warning. Populate STAGING_DIM_AP_DETAIL - RADIO: {ACAA99C4-272C-4641-BF44-B4D4409D1EFB}: The expression "(DT_STR,8,1252)((DT_I4)[SUBSTRING](#85,5,2) == 1 ? (DT_STR,8,1252)"January" : ((DT_I4)[SUBSTRING](#85,5,2) == 2 ? (DT_STR,8,1252)"February" : ((DT_I4)[SUBSTRING](#85,5,2) == 3 ? (DT_STR,8,1252)"March" : ((DT_I4)[SUBSTRING](#85,5,2) == 4 ? (DT_STR,8,1252)"April" : ((DT_I4)[SUBSTRING](#85,5,2) == 5 ? (DT_STR,8,1252)"May" : ((DT_I4)[SUBSTRING](#85,5,2) == 6 ? (DT_STR,8,1252)"June" : ((DT_I4)[SUBSTRING](#85,5,2) == 7 ? (DT_STR,8,1252)"July" : ((DT_I4)[SUBSTRING](#85,5,2) == 8 ? (DT_STR,8,1252)"August" : ((DT_I4)[SUBSTRING](#85,5,2) == 9 ? (DT_STR,8,1252)"September" : ((DT_I4)[SUBSTRING](#85,5,2) == 10 ? (DT_STR,8,1252)"October" : ((DT_I4)[SUBSTRING](#85,5,2) == 11 ? (DT_STR,8,1252)"November" : (DT_STR,8,1252)"December")))))))))))" will always result in a truncation of data. The expression contains a static truncation (the truncation of a fixed value).   Dimension Finance - Load STAGING_DIM_AP_DETAIL_DERIVED_COLUMNS.dtsx 0 0 

This is the derived column expression I'm using:

(DT_STR,8,1252) ((DT_I4)SUBSTRING([POST_PERIOD],5,2) == 1? (DT_STR,8,1252) "January" : ((DT_I4)SUBSTRING([POST_PERIOD],5,2) == 2 ? (DT_STR,8,1252) "February" : ((DT_I4)SUBSTRING([POST_PERIOD],5,2) == 3 ? (DT_STR,8,1252) "March" :((DT_I4)SUBSTRING([POST_PERIOD],5,2) == 4 ? (DT_STR,8,1252) "April":((DT_I4)SUBSTRING([POST_PERIOD],5,2) == 5 ? (DT_STR,8,1252) "May" : ((DT_I4)SUBSTRING([POST_PERIOD],5,2) == 6 ? (DT_STR,8,1252) "June" : ((DT_I4)SUBSTRING([POST_PERIOD],5,2) == 7 ? (DT_STR,8,1252) "July" : ((DT_I4)SUBSTRING([POST_PERIOD],5,2) == 8 ? (DT_STR,8,1252) "August":((DT_I4)SUBSTRING([POST_PERIOD],5,2) == 9 ? (DT_STR,8,1252) "September":((DT_I4)SUBSTRING([POST_PERIOD],5,2) == 10? (DT_STR,8,1252) "October": ((DT_I4)SUBSTRING([POST_PERIOD],5,2) == 11 ? (DT_STR,8,1252) "November" : (DT_STR,8,1252)"December")))))))))))

Some info that might be of some help in the matter...the POST_PERIOD field is a VARCHAR(8), the POST_PERIOD_MONTH field is also a VARCHAR(8).

There is a computed column Any help would be appreciated once again!!!

Hi Robinson,

Based on the error message, we can know the error is caused by there is a value #85in POST_PERIOD field.
When the #85 value is returned by POST_PERIOD field, it cannot validate the expression [SUBSTRING](#85,5,2). Because it doesnt have 5 characters.

So to fix this issue, please make sure all values in the POST_PERIOD field with this string format YYYYMM (i.e., 201503). Or if there are other format values, we can convert them to a fixed month, like Dec. The expression is for your references:

(DT_STR,8,1252) (len([POST_PERIOD])>=5?((DT_I4)SUBSTRING([POST_PERIOD],5,2) == 1? (DT_STR,8,1252) "January" : (DT_I4)SUBSTRING([POST_PERIOD],5,2) == 2 ? (DT_STR,8,1252) "February" : (DT_I4)SUBSTRING([POST_PERIOD],5,2) == 3 ? (DT_STR,8,1252) "March" :(DT_I4)SUBSTRING([POST_PERIOD],5,2) == 4 ? (DT_STR,8,1252) "April":(DT_I4)SUBSTRING([POST_PERIOD],5,2) == 5 ? (DT_STR,8,1252) "May" : (DT_I4)SUBSTRING([POST_PERIOD],5,2) == 6 ? (DT_STR,8,1252) "June" : (DT_I4)SUBSTRING([POST_PERIOD],5,2) == 7 ? (DT_STR,8,1252) "July" : (DT_I4)SUBSTRING([POST_PERIOD],5,2) == 8 ? (DT_STR,8,1252) "August":(DT_I4)SUBSTRING([POST_PERIOD],5,2) == 9 ? (DT_STR,8,1252) "September":(DT_I4)SUBSTRING([POST_PERIOD],5,2) == 10? (DT_STR,8,1252) "October": (DT_I4)SUBSTRING([POST_PERIOD],5,2) == 11 ? (DT_STR,8,1252) "November" : (DT_STR,8,1252)"December"):"Dec")

Thanks,
Katherine Xiong

If you have any feedback on our support, please click here.