SSIS DATEPART derived column

  • 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

  • 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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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

  • 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?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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.

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

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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!

  • 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

  • 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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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!

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

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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

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