July 24, 2011 at 9:24 pm
Hi Team,
I am working on SSIS project where I want to set the variable values and use these variables in my transformations.
@MONTH_START_DATE=CONVERT(VARCHAR(10),DATEADD(DD,-(DAY(DATEADD(MM,1,GETDATE()))-1),DATEADD(MM,-1,GETDATE())),120),
@MONTH_END_DATE=CONVERT(VARCHAR(10),DATEADD(DD,-(DAY(GETDATE())),GETDATE()),120),
@ONE_YEAR_BACK_DATE=CONVERT(VARCHAR(10),DATEADD(DD,-(DAY(DATEADD(MM,1,GETDATE()))-1),DATEADD(MM,-12,GETDATE())),120)
Can anyone tell me how to create this expression in SSIS?
Thanks in Advance
Joyking
July 25, 2011 at 7:50 am
Personally I wouldn't use an expression just run your T-SQL in a Execute SQL task - e.g.
SELECT CONVERT(VARCHAR(10),DATEADD(DD,-(DAY(DATEADD(MM,1,GETDATE()))-1),DATEADD(MM,-1,GETDATE())),120) as Column1,CONVERT(VARCHAR(10),DATEADD(DD,-(DAY(GETDATE())),GETDATE()),120) as column2,
CONVERT(VARCHAR(10),DATEADD(DD,-(DAY(DATEADD(MM,1,GETDATE()))-1),DATEADD(MM,-12,GETDATE())),120) as column3
Then set the Execute SQL task "Result set" to "single row", you can then use the "result set" item on the left to map the query column values to SSIS variables.
July 25, 2011 at 7:58 am
It would also help if you would explain 'in English' what that expression does, rather than leaving it as SQL puzzle of the day, to help those here who do not have the time to decrypt the code before thinking about possible SSIS equivalents.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 25, 2011 at 8:36 am
The "Expression Builder" has date functions in it. They are very similar to the T-SQL ones. You should be able to build it there.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 25, 2011 at 8:47 am
SinisterPenguin (7/25/2011)
Personally I wouldn't use an expression just run your T-SQL in a Execute SQL task - e.g.SELECT CONVERT(VARCHAR(10),DATEADD(DD,-(DAY(DATEADD(MM,1,GETDATE()))-1),DATEADD(MM,-1,GETDATE())),120) as Column1,CONVERT(VARCHAR(10),DATEADD(DD,-(DAY(GETDATE())),GETDATE()),120) as column2,
CONVERT(VARCHAR(10),DATEADD(DD,-(DAY(DATEADD(MM,1,GETDATE()))-1),DATEADD(MM,-12,GETDATE())),120) as column3
Then set the Execute SQL task "Result set" to "single row", you can then use the "result set" item on the left to map the query column values to SSIS variables.
I always avoid connecting to SQL Server in SSIS if I'm not going to be doing anything with a database. In such circumstances, it's better, in my opinion, to use the tools provided in SSIS. Second choice would be to use a Script task - although I admit that it's somewhat fiddly.
John
July 25, 2011 at 8:56 am
Hi Team,
Thanks for your reply and Sorry for the incomplete problem statement.
I am converting the Stored procedure to SSIS Packages in order to leverage the performance advantages.
So, above queries will set the variable values. I want to create these three variable in SSIS package and assign the values to it.
So I need the SSIS expression to get the same result.
Thanks in advance.
JoyKing...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply