[ssis] SSIS expression: convert date to string

I'm new to SSIS and I'm trying to convert a GetDate() to string "DD-MM-YYYY". This is the expression I've built so far:

(DT_WSTR, 8)  DAY( GETDATE()) + "-" + (DT_WSTR, 8)  (MONTH(GETDATE()) - 1) + "-" + (DT_WSTR, 8) YEAR(GETDATE())

The problem I've got is Month() converts the Month "23-4-2013" to a single character when I want it in Double character, same as day. How do i make it into a double character no matter what month it is?

This question is related to ssis expression getdate

The answer is


for the sake of completeness, you could use:

(DT_STR,8, 1252) (YEAR(GetDate()) * 10000 + MONTH(GetDate()) * 100 + DAY(GetDate()))

for YYYYMMDD or

RIGHT("000000" + (DT_STR,8, 1252) (DAY(GetDate()) * 1000000 + MONTH(GetDate()) * 10000 + YEAR(GetDate())), 8)

for DDMMYYYY (without hyphens). If you want / need the date as integer (e.g. for _key-columns in DWHs), just remove the DT_STR / RIGTH function and do just the math.


@[User::path] ="MDS/Material/"+(DT_STR, 4, 1252) DATEPART("yy" , GETDATE())+ "/" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , GETDATE()), 2) + "/" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , GETDATE()), 2)

If, like me, you are trying to use GETDATE() within an expression and have the seemingly unreasonable requirement (SSIS/SSDT seems very much a work in progress to me, and not a polished offering) of wanting that date to get inserted into SQL Server as a valid date (type = datetime), then I found this expression to work:

@[User::someVar] = (DT_WSTR,4)YEAR(GETDATE()) + "-"  + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + "-"  + RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2) + " " + RIGHT("0" + (DT_WSTR,2)DATEPART("hh", GETDATE()), 2) + ":" + RIGHT("0" + (DT_WSTR,2)DATEPART("mi", GETDATE()), 2) + ":" + RIGHT("0" + (DT_WSTR,2)DATEPART("ss", GETDATE()), 2)

I found this code snippet HERE


Something simpler than what @Milen proposed but it gives YYYY-MM-DD instead of the DD-MM-YYYY you wanted :

SUBSTRING((DT_STR,30, 1252) GETDATE(), 1, 10)

Expression builder screen:

enter image description here


Examples related to ssis

Visual Studio 2017 does not have Business Intelligence Integration Services/Projects 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data) Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot Microsoft.ACE.OLEDB.12.0 is not registered SSIS Excel Connection Manager failed to Connect to the Source The value violated the integrity constraints for the column Error: 0xC0202009 at Data Flow Task, OLE DB Destination [43]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21 how to resolve DTS_E_OLEDBERROR. in ssis SSIS - Text was truncated or one or more characters had no match in the target code page - Special Characters SSIS expression: convert date to string

Examples related to expression

How do I remove all non-ASCII characters with regex and Notepad++? SSRS Expression for IF, THEN ELSE Regex to get the words after matching string Regular expression to match a word or its prefix SSIS expression: convert date to string Are complex expressions possible in ng-hide / ng-show? Change some value inside the List<T> XPath - Difference between node() and text() javascript - match string against the array of regular expressions Spring cron expression for every after 30 minutes

Examples related to getdate

JDBC ResultSet: I need a getDateTime, but there is only getDate and getTimeStamp SSIS expression: convert date to string SELECT CONVERT(VARCHAR(10), GETDATE(), 110) what is the meaning of 110 here? Rounding SQL DateTime to midnight How to print GETDATE() in SQL Server with milliseconds in time?