Link to home
Create AccountLog in
Microsoft SQL Server

Microsoft SQL Server

--

Questions

--

Followers

Top Experts

Avatar of banjara15
banjara15

SSIS Expression

I am building a SSIS package. As a business requirement I have to write these expression for the dates coming in the dataflow: For example the name of the date column is "fillDt"

1) dayOfQuarterCnt: this is the count of days for every quarter. For example
                     If the fillDt is "2002-03-04" the count will be 63
                     If the fillDt is "2002-06-20" the count will be 81

2) dayOfYearCnt: this is the count of days for each year. For example
                     If the fillDt is "2002-03-04" the count will be 63
                     If the fillDt is "2002-06-20" the count will be 171

3) monthOfQuarterCnt: this is the count of the month of each quarter which could be 1,2 or 3.
    For example:
                    If the fillDt is "2002-03-31" the count will be 3
                    If the fillDt is "2002-06-30" the count will be 3
                    If the fillDt is "2002-07-13" the count will be 1

4) quarterNm: This is a string for the name of the quarter. For example
                    If the fillDt is "2002-03-04" the name will be "2002 Q1"
                    If the fillDt is "2002-06-20" the name will be "2002 Q2"
                    If the fillDt is "2002-07-13" the name will be "2002 Q3"

5) quarterOfYearCnt: This is the count of the quarter. For example
                    If the fillDt is "2002-03-04" the quarter count will be 1
                    If the fillDt is "2002-06-20" the quarter count will be 2
                    If the fillDt is "2002-10-06" the quarter count will be 4

6) quarterStartDt: This is the quarter start date, based on the incoming date. For example
                   If the fillDt is "2002-03-04" the "quarterStartDt" will be "2002-01-01"
                   If the fillDt is "2002-06-20" the "quarterStartDt" will be "2002-04-01"

7) quarterStopDt: This is the quarter end date, based on the incoming date. For example
                   If the fillDt is "2002-03-04" the "quarterStopDt" will be "2002-03-31"
                   If the fillDt is "2002-06-20" the "quarterStopDt" will be "2002-06-30"

Guys, I need to complete it by Monday... please help!!!

Zero AI Policy

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

Avatar of Reza RadReza Rad🇳🇿

Add derived column transformation after your data source
add a derived column per each column you want as above,
add conditional statement for each of them.
I made conditional statement for first case, but you can do it simply for others.

this is simple syntax for conditional statement expressions:
«boolean_expression» ? «when_true» : «when_false»
«boolean_expression»  -> is your conditional expression, for example if you want to know that your column is equal to date "2002-03-04", you can use this:
DATEDIFF( "dd", [fillDt] ,(DT_DBTIMESTAMP)"2002-03-04" )==0
«when_true» -> is the output when above condition is true, this can be 63 in this case
«when_false» -> is the output when above condition is false, (NOTE HERE IS THAT YOU CAN USE ANOTHER CONDITIONAL EXPRESSION INSTEAD OF THIS VALUE)

look at full expression for dayOfQuarterCnt that i attached below:


DATEDIFF( "dd", [fillDt] ,(DT_DBTIMESTAMP)"2002-03-04" )==0  ? 63 : (DATEDIFF( "dd", [fillDt] ,(DT_DBTIMESTAMP)"2002-06-20" )==0  ? 81 : 0)

Open in new window

Avatar of banjara15banjara15

ASKER

reza_red,

I have "fillDt" as the date column comingin the data flow. So based on what ever dates are recieved I need to do the computation. In the first case the example I have used shows the result after the computation. If the fill date values is "2002-03-04" which is 4th of March 2002. which means 31 days of Jan + 28 Days of Feb + 4 days of Mar = 63rd day of the quarter. I need to write an expression to do the computation for any date. The  "2002-03-04" is just an example, it is not a fixed value. The "fillDt" could be any date...
Avatar of Reza RadReza Rad🇳🇿

OK,
I got it now, sorry for mis understanding,
does fillDt comes from sql server source? I asked this because calculating above requests is simpler with builtin sql server functions.
Reward 1Reward 2Reward 3Reward 4Reward 5Reward 6

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.

Avatar of Reza RadReza Rad🇳🇿

Ok,
I made first one with expressions:


DATEDIFF("DAY",(DT_DATE)(((DT_WSTR,4)YEAR(fillDt)) + "-" + ((DT_WSTR,2)(((DATEPART("QUARTER",fillDt) - 1) * 3) + 1)) + "-1"),fillDt)

Open in new window

Avatar of Reza RadReza Rad🇳🇿

OK,
I made it at last,
that was tedius work, take 30 minutes from me !

add derived columns as below with these expressions

here:
1-
dayOfQuarterCnt
expression:
DATEDIFF("DAY",(DT_DATE)(((DT_WSTR,4)YEAR(fillDt)) + "-" + ((DT_WSTR,2)(((DATEPART("QUARTER",fillDt) - 1) * 3) + 1)) + "-1"),fillDt)
2-
dayOfYearCnt
expression:
DATEDIFF("DAY",(DT_DATE)(((DT_WSTR,4)YEAR(fillDt)) + "-1-1"),fillDt)
3-
monthOfQuarterCnt
expression:
DATEDIFF("MONTH",(DT_DATE)(((DT_WSTR,4)YEAR(fillDt)) + "-" + ((DT_WSTR,2)(((DATEPART("QUARTER",fillDt) - 1) * 3) + 1)) + "-1"),fillDt)
4-
quarterNm
expression:
(DT_WSTR,4)YEAR(fillDt) + " Q" + (DT_WSTR,1)(DATEPART("QUARTER",fillDt))
5-
quarterOfYearCnt
expression:
(DT_WSTR,1)(DATEPART("QUARTER",fillDt))
6-
quarterStartDt
expression:
(DT_DATE)(((DT_WSTR,4)YEAR(fillDt)) + "-" + ((DT_WSTR,2)(((DATEPART("QUARTER",fillDt) - 1) * 3) + 1)) + "-1")
7-
quarterStopDt
expression:
DATEADD("DAY",-1,DATEADD("MONTH",3,(DT_DATE)(((DT_WSTR,4)YEAR(fillDt)) + "-" + ((DT_WSTR,2)(((DATEPART("QUARTER",fillDt) - 1) * 3) + 1)) + "-1")))

screenshot of derived column transformation editor and samle result attached

Regards,
Reza Rad




222.jpg
111.jpg
Avatar of banjara15banjara15

ASKER


Reza rad,
You are genius, I never expected such a quick reply....thanks a lot...I'll try all these expressions and let you know...

Thanks again :-)
Free T-shirt

Get a FREE t-shirt when you ask your first question.

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

Avatar of banjara15banjara15

ASKER

Reza rad,

I tested the expressions, the results are included in the attached spreadsheet. I have also added my comments...

Thanks
SSIS-Expression-Test-Result.xls
ASKER CERTIFIED SOLUTION
Avatar of Reza RadReza Rad🇳🇿

Link to home
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
Create Account
Avatar of banjara15banjara15

ASKER


I'll do that, thanks again for all your help!!!!
Avatar of banjara15banjara15

ASKER


Reza rad,

I am really grateful to you for your prompt reply and resolving the problem .... My million thanks to you :)
Reward 1Reward 2Reward 3Reward 4Reward 5Reward 6

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.

Avatar of Reza RadReza Rad🇳🇿

Glad to help,
Regards,

Microsoft SQL Server

Microsoft SQL Server

--

Questions

--

Followers

Top Experts

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.