July 2, 2018 at 2:37 pm
I'm building a SSIS pkg that runs every Monday; it picks up a .csv file from a share which gets saved on a network share by the business the previous Wednesday, AND, MORE IMPORTANT, it has a date stamp of 6/23/18 at the end of the file, which is the pay week end period for this report. i.e. if the SSIS ran today, 7/2/18, it needs to look for a .csv file that has a date stamp at the end of the file name of 06232018 (two Saturdays ago). , I've built the package fully, except the only piece I'm struggling with is creating a variable in expression builder to append a default date to the end of the file name to be 06232018 in expression builder. I looked for code in google but couldn't find anything that helps me.
source file: \\networkshare\inbound\weekly_file_06232018.csv
July 2, 2018 at 3:19 pm
I haven't used SSIS in years, but the following works in SQL and there should be a similar expression in the SSIS Expression Builder. You may need to adjust the offset to get the precise delay that you want.
DECLARE @ARandomSaturday DATE = '20180630'
SELECT DATEADD(WEEK, DATEDIFF(WEEK, @ARandomSaturday, GETDATE()) - 2, @ARandomSaturday)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 9, 2018 at 7:35 am
Thanks for the post, but that doesn't exactly help me, as I need the ssis version builder syntax for it.
July 9, 2018 at 7:45 am
Drew is right, Put that in an Execute SQL task and assign it to a variable in the package, then in the Expression Builder, you can reference the variable. Set Delay Validation to True on the component and you're good. Also, you can tweak Drew's code to come up with a real filename if needed.
SELECT 'FileName_' + cast(DATEADD(WEEK, DATEDIFF(WEEK, @ARandomSaturday, GETDATE()) - 2, @ARandomSaturday) as varchar(20))
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 9, 2018 at 9:30 am
Oh, got it! One more question though (and pardon my ignorance): Drew hard coded the variable @ARandomSaturday to a Saturday' specific date. My thought is; Does it need to be any Saturday' date for the expression to subtract two Saturdays from present date?
July 9, 2018 at 11:05 am
Depends on which day is the first day of the week. I threw this together to get 2 Saturdays ago, but I'm sure there's a better way. Just ran out of time
select cast(getdate() - datepart(DW, getdate()) - @@datefirst - case when @@DateFirst = 1 then 7 else 0 end as date)
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 9, 2018 at 11:33 am
this expression returns '2018-06-30' which is correct, but how do I convert it to '06302018', which is the format date at the end of the file?
select cast(getdate() - datepart(DW, getdate()) - @@datefirst - case when @@DateFirst = 1 then 7 else 0 end as date)
July 9, 2018 at 12:27 pm
This will convert it to YYYYMMDD which would be easier to find if more than one year is in the folder.
select convert(varchar(10), getdate() - datepart(DW, getdate()) - @@datefirst - case when @@DateFirst = 1 then 7 else 0 end , 112)
Otherwise, some real ugly stuff
select Replace(convert(varchar(10), cast(getdate() - datepart(DW, getdate()) - @@datefirst - case when @@DateFirst = 1 then 7 else 0 end as date), 110),'-','')
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 9, 2018 at 2:01 pm
I agree with you on the format should be YYYYMMDD. I will try to make the business stake holder to abide to it. Thanks! much appreciated.
July 10, 2018 at 5:46 am
We use the Expression Builder for a variable in SSIS to generate a date-stamp
(DT_WSTR,4) YEAR(GETDATE()) +
RIGHT("0" + (DT_WSTR,2) MONTH(GETDATE()),2) +
RIGHT("0" + (DT_WSTR,2) DAY(GETDATE()),2)
Rearrange the pieces and you can get it into MMDDYYYY format if you need that.
Since you know the job runs on Monday, the 2nd Saturday previous is 9 days earlier - you can simply subtract 9 days from GETDATE to find that Saturday and substitute it into the Expression Builder above. But if it's possible that the job would be run on days other than Monday, I'd use a SQL Task to populate a date variable with the proper Saturday date, and then substitute that variable into the Expression Builder.
July 10, 2018 at 7:04 am
gvoshol 73146 - Tuesday, July 10, 2018 5:46 AMWe use the Expression Builder for a variable in SSIS to generate a date-stamp(DT_WSTR,4) YEAR(GETDATE()) +
RIGHT("0" + (DT_WSTR,2) MONTH(GETDATE()),2) +
RIGHT("0" + (DT_WSTR,2) DAY(GETDATE()),2)Rearrange the pieces and you can get it into MMDDYYYY format if you need that.
Since you know the job runs on Monday, the 2nd Saturday previous is 9 days earlier - you can simply subtract 9 days from GETDATE to find that Saturday and substitute it into the Expression Builder above. But if it's possible that the job would be run on days other than Monday, I'd use a SQL Task to populate a date variable with the proper Saturday date, and then substitute that variable into the Expression Builder.
the only issue with hardcoding like that is if it fails on Saturday and no one gets to it until Monday
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 10, 2018 at 12:15 pm
(DT_WSTR,4) YEAR(GETDATE()) +
RIGHT("0" + (DT_WSTR,2) MONTH(GETDATE()),2) +
RIGHT("0" + (DT_WSTR,2) DAY(GETDATE()),2)
Rearrange the pieces and you can get it into MMDDYYYY format if you need that.
Since you know the job runs on Monday, the 2nd Saturday previous is 9 days earlier - you can simply subtract 9 days from GETDATE to find that Saturday and substitute it into the Expression Builder above. But if it's possible that the job would be run on days other than Monday, I'd use a SQL Task to populate a date variable with the proper Saturday date, and then substitute that variable into the Expression Builder.
In this case, the SQL task is the way to go in case the job needs to be re-run on a date other than Monday.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply