Problems releated to date format at SISS during an ETL (1 Viewer)

[maria.1395]

Hi there!

I would like to change this date: 2020-03-25 00:00:00 (the data type on SSIS is int to this type of column)
Into this kind of date 2021 May
Through derived column
I have been trying to use dateformat but there was no result.

Thank you very much.

 

[Isaac]

What about DT_STR(Year(DateColumn)) + ' ' + Expression
where Expression is something like this

I know that's not a full answer, but it might get you close and is probably what I would try.

Honestly, in SSIS I typically avoid derived columns and transformations, preferring instead to load the raw data into a staging table and then use regular T-SQL to do all manipulations and routing. I don't have any sophisticated justification for this other than, this way you only have to learn one coding platform (t-sql) rather than two (t-sql and ssis expression language).

Well, also: It's just that much less redundant stuff to learn in two entire platform (not just the ssis expression, but all the related skills of troubleshooting those particular ssis tasks...when you could just skip all that, and load raw data always). I see some people doing long and tedious projects involving complex ssis routing, splitting, deriving, and transforming - and I think to myself "why not load the raw data unchanged and spend all your time doing that in t-sql rather than doubling your work?"

 

[Minty]

In T-Sql

DatePart(Year(YourDateColumn)) + ' ' + DATENAME(Month, YourDateColumn) as YearMonth

 

[Isaac]

@maria.1395 Minty's post serves as a nice advertisement for why to use t-sql rather than the more creative SSIS tasks with their expressions - that's looking a lot easier isn't it! For example, the first thing you notice is that no text conversion (of the first part) is required to concatenate it with the second part.

 

[Minty]

Actually - as an Edit you do need to cast the Year as text as it returns an integer... (Teach me to air code)

Cast(DATEPART(year,YourDateColumn) as nvarchar(4)) + ' ' + DATENAME(month,YourDateColumn) as YearMonth

 

[Isaac]

Actually - as an Edit you do need to cast the Year as text as it returns an integer... (Teach me to air code)

Cast(DATEPART(year,YourDateColumn) as nvarchar(4)) + ' ' + DATENAME(month,YourDateColumn) as YearMonth

You had me thinking positively assume an implicit conversion would work - ha . Don't have SSMS open so can't try it out now.
Still, if I were maria I'd go for moving raw data to t-sql and then doing it there, it drives me nuts trying to mentally keep track of ssis expressions language + t-sql, rather than just t-sql. It's like listening to Spanglish.

 

[maria.1395]

@maria.1395 Minty's post serves as a nice advertisement for why to use t-sql rather than the more creative SSIS tasks with their expressions - that's looking a lot easier isn't it! For example, the first thing you notice is that no text conversion (of the first part) is required to concatenate it with the second part.

ooooooooh I see... Thank you very much. I will try what you say

 

[Isaac]

ooooooooh I see... Thank you very much. I will try what you say

As a reminder, like Minty pointed out, I may have been wrong about not needing to cast DATEPART (in t-sql) to string before concatenating it (Minty has tested it, I cannot right now).

Unless you were just referring to my general advice of loading the raw data straight to SQL Server without transformation in SSIS, which I do stand by.

Good luck with your project!