SSIS Expressions

Question

SSIS Expressions

nthetengm

SSC Enthusiast

Points: 173
More actions
March 25, 2010 at 2:46 am

#231127

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_dateBETWEENDATEADD(MONTH,-6,GETDATE()) AND GETDATE()"

Viewing 9 posts - 1 through 8 (of 8 total)

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

Todasdasdfd asfasf Ten Centuries Points: 1340 More actions · Answer 1

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[/url]
Are you doing Data Warehouse ETL with SSIS? Don't use the slow SCD Wizard, use the Kimball Method SCD component instead.

veerendramsbi Valued Member Points: 71 More actions · Answer 2

veerendramsbi

Valued Member

Points: 71

January 7, 2011 at 3:05 am

#1270522

i want to real time scenarios

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

veerendramsbi (1/7/2011)
i want to real time scenarios

Don't we all?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

xequence Valued Member Points: 60 More actions · Answer 4

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.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

xequence Valued Member Points: 60 More actions · Answer 6

xequence

Valued Member

Points: 60

May 26, 2011 at 8:28 am

#1330356

DATEADD("month",4,(DT_DATE)[column])

Kum@R Marlapati SSC Journeyman Points: 82 More actions · Answer 7

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

kiran.vaichalkar Ten Centuries Points: 1056 More actions · Answer 8

@kumar-2/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 https://picasaweb.google.com/103673862776965329044/March92014#5988691608810460146%5B/img%5D.

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

IS that SSIS dont understand DATEDIFF() ?