SSIS DATEPART derived column

Question

SSIS DATEPART derived column

justintime

Ten Centuries

Points: 1060
More actions
January 14, 2010 at 1:36 pm

#137493

i am fairly new to SSIS and trying to pull the year (and eventually month and day) from a datetime field in my table and add the derived column to an existing field in the same table.
i understand the basics (i think) but it is creating new records for the derived column, not adding them to my existing 'dateYear' column. when it creates these new records in the table, all other fields are NULL with the exception of 'dateYear'.
in the derived column editor i tell it to Replace 'dateYear' and am using the expression DATEPART("yyyy",GETDATE()). do i need to change my expression to somehow tell it to pull the year from 'datestamp'? the name of my datetime field is 'datestamp'
using SSIS 2005
please let me know if you need more info
-js

Viewing 11 posts - 1 through 10 (of 10 total)

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

Phil Parkin SSC Guru Points: 246993 More actions · Answer 1

If you double click on your destination in your dataflow and select Mappings, you should see the new derived column as an available input column - just change the mapping there.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

justintime Ten Centuries Points: 1060 More actions · Answer 2

i think that everything is mapped correctly. i even stripped everything down to be as simple as i could make it. it is still trying to add new records containing only the new derived 'dateYear' for some reason. this is not possible because i have an employeeID field that is the primary key with Allow NULLS unchecked so the package is bombing on that error. if i check Allow NULLS on employeeID in the table and run the package, then it creates the new records, with only 'dateYear' filled in.

source

SELECT

datestamp, dateYear

FROM

dbo.emDetail

Derived Column Name

dateYear

Derived Column

Replace 'dateYear'

Expression

DATEPART("yyyy",GETDATE())

destination

SELECT

datestamp, dateYear

FROM

dbo.emDetail

Phil Parkin SSC Guru Points: 246993 More actions · Answer 3

I think I may have misunderstood your requirement. Are you trying to UPDATE records which are already on the database, rather than doing an INSERT?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

justintime Ten Centuries Points: 1060 More actions · Answer 4

yes. update same table. i currently have datestamp (populated), dateYear (blank), dateMonth (blank) (blank), dateDay (blank). i want to extract the different blank parts from the datestamp. i would just create a new table but i will need to run this package regularly as i will be receiving new data on a regular basis.

Phil Parkin SSC Guru Points: 246993 More actions · Answer 5

To update existing data, you need to use an OLEDB Command transformation. The various different Destination components are for INSERTs only.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

justintime Ten Centuries Points: 1060 More actions · Answer 6

good to know. i was beating my head against the desk the last couple of days.

i don't supposed i could trouble you for the sql command to go about extracting the year from the datestamp? would it be a DATEPART expression? if i had that, i could figure out the rest.

thanks!

justintime Ten Centuries Points: 1060 More actions · Answer 7

i just tried this statement in SSIS and SSMS

update dbo.emDetail

set dateYear = DATEPART("year",'datestamp')

and am getting the following error

Conversion failed when converting datetime from character string

Phil Parkin SSC Guru Points: 246993 More actions · Answer 8

Is DateStamp a column name in emDetail?

If so, your syntax is going to be more like this:

update dbo.emDetail

set dateYear = DATEPART('year',datestamp)

Remember single quotes for literals in T-SQL.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

justintime Ten Centuries Points: 1060 More actions · Answer 9

Msg 1023, Level 15, State 1, Line 2

Invalid parameter 1 specified for datepart.

i changed it to

set dateYear = DATEPART([year],[datestamp])

and it works fine.

thanks Phil!

Phil Parkin SSC Guru Points: 246993 More actions · Answer 10

Oops, sorry. Glad you got it in the end.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel