SSIS Expressions and Quarter Start Dates

the ultimate county fair strategery game - the quarter plow

SSIS has a pretty powerful expression language.  Of course, it's not exactly the same expression language as in SSRS.  And it's not exactly C# or VB.  It is C-like though, so it's not that bad to get the hang of.

Anyway, it can be pretty useful.  Some common uses of expressions within SSIS are when creating dynamic variables (evaluated as an expression) and derived column values in the pipeline.  This post details an example of using an SSIS expression to calculate the quarter start date for each row in the pipeline as it passes through a Derived Column component.  This same approach could easily be used to apply business rules to dates or other values in your source data on the way to your fact table.
First off, within BIDS (or SQL Server Data Tools in SQL2012) it's actually easier to develop your expressions in a variable first so that you can easily test the expression without actually having to run the package.  Once the expression is working as desired in the variable, you can simply copy the code into the needed derived column component and tweak as needed to reference your desired date column in the pipeline.  By working within the variable first, you get to evaluate the expression repeatedly (click "Evaluate Expression" button) until you've got your logic bulletproof. 

Once you have the expression working, just add it as a new column to the derived column component within your dataflow task. 

Once the derived column is set, you'll want to test a few data loads just to make sure everything is as planned. You can easily do this by adding a data viewer to the pipeline just after your derived column component.  The data viewer acts as a breakpoint in the flow and will pause the execution once populated.  Upon pausing, you can easily inspect the output rows from the derived column and make sure everything is in order.

Ok, so that's the package dev and testing bit.  Here are some details on the actual expression code.  To get the quarter start date, you have to do a bit of date manipulation.  Technically, we could have done this via a lookup if we had quarter start date in a date dimension.  Or we could have done this same date manipulation in T-SQL within the data flow source query.  But today we'll focus on the SSIS expressions.

For this one, I used the DATEPART function to get the quarter and then do a bit of math for the proper month:

(DT_STR,4,1252)(YEAR(GETDATE())) 
+"-"
+ RIGHT("0" + (DT_STR,2,1252)((((DATEPART("q", (DT_DATE)"2012-02-25" )-1)*3)+1))+ "-01",5)

I plugged in the (DT_DATE)"2012-02-25" in the last part so that I could try different dates and easily test to make sure the expression was working as desired. For the final product, just need to replace the hard-coded date with your incoming date.  In this case, I'm just passing in GETDATE():

(DT_STR,4,1252)(YEAR(GETDATE())) 
+"-"
+ RIGHT("0" + (DT_STR,2,1252)((((DATEPART("q", GETDATE() )-1)*3)+1))+ "-01",5)

That's it.  Not to difficult.  The date math to use the quarter to get the right month was the tricky bit.  But even that wasn't that bad. 

Hope this was helpful.