December 11, 2014 at 12:45 pm
I am creating a SSIS package to run some Stored procedure, generate tables and then export those tables to a csv file. I need to pass in date parameters and i created a bunch of variables. I need the date of the first and last day of the previous month and the first and last day of two months ago. The queries that i am using are:
select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) --Previous Months first day
select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, -1)-- Previous Months Last Day
select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-2, 0) --Two months Ago first day
select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-2, -1) --Two months ago Last Day
These queries work as expected in SSMS and return the correct results.
In SSIS i created variables and entered the queries as the Expression to generate the correct value for each variable. For a few of them i get the correct information but for some i don't get the correct value.
Steps:
1. Change Data Type to DateTime. -> Value automatically defaults to current Date and time.
2. Enter query in Expression (Select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-2, 0) (Expected - Value should change to 10/1/2014 12:00 AM ) -> Value doesn't change
Steps:
1. Change Data Type to DateTime. -> Value automatically defaults to current Date and time.
2. Delete Value
3. Enter query in Expression (Select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-2, 0) (Expected - Value should change to 10/1/2014 12:00 AM ) -> Value changes to 12/30/1899
Steps:
1. Change Data Type to DateTime. -> Value automatically defaults to current Date and time.
2. Delete Value, enter in expected value 10/1/2014 12:00 AM
3. Enter query in Expression (Select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-2, 0) -> Value changes to 10/1/2014
December 11, 2014 at 1:22 pm
You can't use T-SQL in SSIS expressions you need to use the SSIS Expression Language. This blog post is a good place to start looking at this.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 11, 2014 at 1:30 pm
SSIS expressions do not accept basic T-SQL queries to calculate things like this. You have to use full SSIS expressions.
Now why your code worked in some and not in others I am not really sure. But an expression to get the first day of the last month would be something like this:
DATEADD( "d", - (DAY(GETDATE())) +1, DATEADD("month",-1,GETDATE()) )
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply