Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SSIS DATEPART derived column Expand / Collapse
Author
Message
Posted Thursday, January 14, 2010 1:36 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 8:54 PM
Points: 48, Visits: 232
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
Post #847901
Posted Friday, January 15, 2010 1:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 2:06 PM
Points: 5,047, Visits: 11,797
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #848113
Posted Friday, January 15, 2010 6:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 8:54 PM
Points: 48, Visits: 232
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
Post #848230
Posted Friday, January 15, 2010 7:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 2:06 PM
Points: 5,047, Visits: 11,797
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #848239
Posted Friday, January 15, 2010 7:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 8:54 PM
Points: 48, Visits: 232
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.
Post #848244
Posted Friday, January 15, 2010 7:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 2:06 PM
Points: 5,047, Visits: 11,797
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #848250
Posted Friday, January 15, 2010 7:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 8:54 PM
Points: 48, Visits: 232
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!
Post #848262
Posted Friday, January 15, 2010 7:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 8:54 PM
Points: 48, Visits: 232
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

Post #848294
Posted Friday, January 15, 2010 8:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 2:06 PM
Points: 5,047, Visits: 11,797
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #848334
Posted Friday, January 15, 2010 8:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 8:54 PM
Points: 48, Visits: 232
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!
Post #848340
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse