SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS DATEPART derived column


SSIS DATEPART derived column

Author
Message
justintime
justintime
SSC-Enthusiastic
SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)

Group: General Forum Members
Points: 152 Visits: 253
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
Phil Parkin
Phil Parkin
SSC-Forever
SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)

Group: General Forum Members
Points: 48539 Visits: 21123
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
justintime
justintime
SSC-Enthusiastic
SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)

Group: General Forum Members
Points: 152 Visits: 253
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
Phil Parkin
SSC-Forever
SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)

Group: General Forum Members
Points: 48539 Visits: 21123
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?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
justintime
justintime
SSC-Enthusiastic
SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)

Group: General Forum Members
Points: 152 Visits: 253
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
Phil Parkin
SSC-Forever
SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)

Group: General Forum Members
Points: 48539 Visits: 21123
To update existing data, you need to use an OLEDB Command transformation. The various different Destination components are for INSERTs only.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
justintime
justintime
SSC-Enthusiastic
SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)

Group: General Forum Members
Points: 152 Visits: 253
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
justintime
SSC-Enthusiastic
SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)

Group: General Forum Members
Points: 152 Visits: 253
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
Phil Parkin
SSC-Forever
SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)

Group: General Forum Members
Points: 48539 Visits: 21123
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
justintime
justintime
SSC-Enthusiastic
SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)

Group: General Forum Members
Points: 152 Visits: 253
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search