SSIS - Splitting the YYYYMMDD date from a comma delimited flat file to multiple columns in OLE DB dest

Question

SSIS - Splitting the YYYYMMDD date from a comma delimited flat file to multiple columns in OLE DB dest

dj1202

SSCommitted

Points: 1829
More actions
April 26, 2012 at 3:04 am

#260412

Hello everyone,
First post here. I'm using SSIS and using a comma delimited flat file as my source, and the date is a string that follows the format: YYYYMMDD. I'm attempting to ultimately extract from this the year, month, and day, as well as the week and quarter, and place them in individual columns in my OLE DB destination. I have created the destination table with the correct data types (varchars and numerics). I attempted using Derived Column to create/convert the columns using the expressions below, but it still returns errors (stops cold at Derived Column).
Derived Column Name | Derived Column | Expression | Data Type
Month | <add as new column> | MONTH((DT_DBTIME)date_id) | four-byte sized integer [DT_14]
Day |<add as new column> | DAY((DT_DBTIME)date_id) | four-byte sized integer [DT_14]
Week |<add as new column> | DATEPART("WW",((DT_DBTIME)date_id)) |four-byte sized integer [DT_14]
Quarter |<add as new column> | DATEPART("QQ",((DT_DBTIME)date_id)) | four-byte sized integer [DT_14]
Year | <add as new column> | YEAR((DT_DBTIME)date_id) | four-byte sized integer [DT_14]
Anyone have any suggestions? Any help would be greatly appreciated.
-Dj

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

What errors are you getting?

Actually, I would urge you not to split the dates into their constituent parts. Store them in the database as datetime, and perform any manipulation on them as and when needed. This has (at least two advantages). First, it takes up less space. Second, it means that there would be no possibility of invalid dates (31st April, for example) creeping into your data.

John

dj1202 SSCommitted Points: 1829 More actions · Answer 2

John Mitchell-245523 (4/26/2012)
What errors are you getting?
Actually, I would urge you not to split the dates into their constituent parts. Store them in the database as datetime, and perform any manipulation on them as and when needed. This has (at least two advantages). First, it takes up less space. Second, it means that there would be no possibility of invalid dates (31st April, for example) creeping into your data.
John

John, thanks for the prompt reply and info. I had to parse the YYYYMMDD from string to a date data type as part of a training exercise I'm completing. I managed to get it done by using two separate derived column tasks. The first one contained the following expression to change the string to a date data type:

(DT_DATE)(SUBSTRING(date_id,1,4) + "-" + SUBSTRING(date_id,5,2) + "-" + SUBSTRING(date_id,7,2))

Named the column above v_date and then used the following expressions in the second derived column task:

MONTH(V_date)

YEAR(V_date)

DAY(V_date)

DATEPART("WW",V_date)

DATEPART("QQ",V_date)

Hope this helps anyone else who runs into this issue!

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

People spend way to much time doing conversions that SQL Server will do on it's own. The following works just fine and will also work with a character based column.

SELECT DATEPART(yy,'20120429'),

DATEPART(mm,'20120429'),

DATEPART(dd,'20120429'),

DATEPART(wk,'20120429'),

DATEPART(qq,'20120429')

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)