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

SSIS Expressions Expand / Collapse
How to write an DATEDIFF @ DATEDIFF function inside a Derived column of an SSIS Package. Im trying to have this both this functions as an expression in a...
Poll ResultsVotes
How to write an DATEDIFF @ DATEDIFF function inside a Derived column of an SSIS Package. Im trying to have this both this functions as an expression in a derived column."DATEDIFF(MONTH,con.commencement_date,@EndDate) AS 'Months In Force'" - "commencement_date BETWEEN DATEADD(MONTH,-6,GETDATE()) A
 
100%
4
Member Votes: 4, Anonymous Votes: 0. You don't have permission to vote within this poll.
Author
Message
Posted Thursday, March 25, 2010 2:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 9, 2013 9:07 AM
Points: 3, Visits: 67
How to write a DATEDIFF function inside a Derived column of an SSIS Package. Im trying to have theses functions as an expression in a derived column."DATEDIFF(MONTH,con.commencement_date,@EndDate) AS 'Months In Force'" - "commencement_date BETWEEN DATEADD(MONTH,-6,GETDATE()) AND GETDATE()"
Post #889608
Posted Thursday, March 25, 2010 10:14 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 22, 2010 9:44 AM
Points: 36, Visits: 109
When you post a question, especially one where you can't get something to work the way you expect, please post complete information. In this case, post the expression you used, the results you obtained (the error text) and the desired result. Without that, we can only guess.

Here's my guess: You haven't read BOL on the SSIS DATEDIFF function to know that it takes a quoted literal instead of a token as the time interval, unlike T-SQL. Most of the SSIS functions that look like T-SQL functions don't operate the same, you'll have to read up on how they're different. Use "month" instead of month.


Todd McDermid - SQL Server MVP, MCSD.Net, MCTS (SQL08BI)
Find great SSIS extensions - most are open source - at SSIS Community Tasks and Components
Are you doing Data Warehouse ETL with SSIS? Don't use the slow SCD Wizard, use the Kimball Method SCD component instead.
Post #889975
Posted Friday, January 7, 2011 3:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 7, 2011 4:33 AM
Points: 1, Visits: 1
i want to real time scenarios
Post #1044255
Posted Friday, January 7, 2011 5:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:28 AM
Points: 13,570, Visits: 10,447
veerendramsbi (1/7/2011)
i want to real time scenarios


Don't we all?




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1044330
Posted Thursday, May 26, 2011 8:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 27, 2012 7:43 AM
Points: 2, Visits: 10
So, there is no solution to this post? Why does it exist in google when i try to find an answer then? Nobody wants to read how to post a correct question. Just makes you sound like a real douche.
Post #1115490
Posted Thursday, May 26, 2011 8:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:26 AM
Points: 13,277, Visits: 12,108
I personally hacked into google's server to add this post to their search engine just to irritate you. If you actually read the post you would realize that the answer in looking at the BOL entry for SSIS datediff function. Maybe you should try the same instead jumping onto a forum and shouting insults.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1115495
Posted Thursday, May 26, 2011 8:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 27, 2012 7:43 AM
Points: 2, Visits: 10
DATEADD("month",4,(DT_DATE)[column])
Post #1115524
Posted Thursday, December 27, 2012 12:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, June 21, 2014 1:34 AM
Points: 2, Visits: 57
You can add that column names as a new columns in your derived column transformation and in expressions you have to use date functions which are available in the transforamtion.

Thanks

kumar
Post #1400484
Posted Sunday, March 9, 2014 12:28 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 24, 2014 12:29 PM
Points: 54, Visits: 101
@KUMAR/All,

can you specify the string we can use as the EXPRESSION under the derived column transformation.

I've also attached a screenshot in the URL.

Please see how it violates the existing DATEDIFF() function in SSMS.

IS that SSIS dont understand DATEDIFF() ?

Post #1549029
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse