Looping Through Excel Files for Correct Year/Period
-
Please look at my Variables and the code used for establishing periods/year. Currently i am in P01 Year 2025 according to the calendar below in pics. I am picking up already data for P02 Year 2025 which is wrong.
-
It is not likely you are going to find someone who will download a random zip file from someone to help with something like this. A better solution is to post your code and where you are having issues and explain the logic with your work.
But my process for what you are doing would be to pull the data in those excel files into a table where you have 4 columns - Fiscal Year, Reporting Period, Period Start, and Period End. You may want more columns (like calendar days), but just with what you gave, that's what I'd do. Then I'd use TSQL to get the dates from SQL rather than from Excel. One big plus is this approach allows you to re-use the date values from the spreadsheet in ANY query without needing to pull it in via SSIS.
My GUESS without looking at your code is that you are doing some bad calculations on the period end dates. I also expect that the format of that Excel is going to just get wider and wider each fiscal which means your integration will need to be modified each year to cover the next fiscal year. IF you do it in a table, you would need to do 1 insert per year for the fiscal year and then you can re-use that table for any script that relies on a fiscal period. MUCH more re-usable and no changes to SSIS due to fiscal year end and excel file getting 2 new columns added. Plus, from my experience, pulling from TSQL in SSIS (or SSMS) is much faster than from Excel.
Just my 2 cents... Doesn't solve your problem, but I am not pulling a random zip file off the internet and even if I did and it is legit (likely is), this level of SSIS support is a bit much for a free forum...
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
To add to the response above, note that it's always a good idea to explicitly state your question(s), rather than expecting readers to go into detective mode as they try to understand exactly what sort of response you are hoping for.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply