It was a monthly batch job to extract data into Excel. Sounds simple. It could have been, if not for the usual complications and unexpected roadblocks.
Again, it was the slipshod specifications. Data from 3 tables were to be extracted into 3 separate Excel files. Looked at the sample files and shook head - the formatting of the headers were different for all 3 files. The design of the columns were unsuitable for certain types of data. I think it's a 5 min work. And obviously none of the users had bothered to open the sample files for a look. If I were to give this for UAT, sure kena firing squad.
But what if... that was what the users truly wanted? They siao siao one sometimes.
After consulting the TL, we were going to change the columns. However, I would leave the headers intact.
I had to use a tool that I had never used before - SQL Server 2005 Integration Services to create a SSIS package. I was not the first to do this, so I had a sample program for reference. Using VB scripting, it runs a query and writes the results line by line into Excel. Sounds simple.
But of course, how could the Sotong be let off lightly? No development phase can get through without a struggle.
I quickly realized that my batch job was going to be far more complex than the sample. The sample was full of hardcodes. If I didn't want to be writing and maintaining 7 different programs, I had better figure out how to use variables. Man, the development tool's UI could use some redesign. It was a struggle to pass variables. Not very intuitive! I had to scour the internet for a very detailed guide. The kind for dummies. Not a friendly learning curve.
I found that passing blank as an input parameter to the batch job wasn't allowed. That's deviating from the usual way we work. Potentially, it might pose a problem if somebody other than me were meddling with it. Which meant that I MUST write the monitoring guide to cover my backside already.
File exists check before generating the file... later, if I have the time! (usually means never)
When I found the Excel Connection Manager, I thought it was a totally cool idea. It could spool data directly into an Excel file. No need for VB script afterall!
I tested using the debug mode. Printed 1 line successfully! Ok continue!
But when I tried to run the proper way by using the SQL Server Agent, it didn't work :( Some cryptic Excel error. Don't know how to add more logs to help myself XD
Ok nevermind. Why don't I try fixing it on SIT instead?
WORSE. The moment Excel was launched, it asked for the installation CD. The prompt will go away if you click Cancel, but I couldn't be incorporating the click into my batch job?? Asked R for access to the CD, but the SIT server couldn't locate the file it wanted.
Great. So this batch job shall join the dll-based ones, cannot be run on SIT. I informed my TL that the batch job was going straight to UAT since there was no way we could test on SIT. He wanted me to ask for help to fix the problem as he didn't want his things to end up like the rest, but I refused flatly. I have tried already, and I didn't have time to try harder. The environment setup has been left hanging for don't know how long already, so I don't think I should even believe it will be fixed for me.
万事有 UAT environment~ Totally wrong, but bo bian. Actually, it saves time because it is almost the same as Production. If it works in UAT, chances are, it will work in Production. And that's all we wish for!
I ran into the same problem :( SHIT. Don't tell me our system cannot support and I got to redo the whole shit in some other way?
Googled the bloody cryptic error message again. There HAS to be a solution. This is not the time to get stuck!
FOUND. Instead of using Integration Services package job step, I should use the command line job step to call the 64–bit version of dtexec.
DIAO!!
I wasn't interested in finding out why. As long as it's working!
Time for the next bomb. The extraction worked fine for narrow date ranges, but failed upon expansion to 1mth's data. Query wasn't slow... I soon discovered the ultimate, stupidest, mother of all limitations -
You cannot spool more than 255 characters into an Excel cell.
I mean, what era is this already??
I want to 跳楼 now.
Blame it on the technical analysis. The users should never have been allowed to enter more than 255 characters.
Ha. Ha. Over the users' dead bodies!
I know. I can truncate and hopes nobody finds out.
Yeah... Right...
Propose to give a flat file instead.
Ok, but only as a last resort.
There was absolutely no problem with spooling into a flat file, but I couldn't figure out how to convert the flat file into Excel. This transplantation is a very simple copy and paste function when performed manually.
You know you are going insane when you start thinking:
You know, I think I would rather do it manually than to go on struggling. It's only once a month.
Shout 救命 also no use. Nobody can help me. Not even Google.
The fucking 3G signal didn't cooperate during office hours. Or colleagues have a greater right to it because they were doing investigations. And I was multi-tasking.
Desperate times called for desperate measures. I could split long data into 255-characters long segments and spool into separate cells first, then concatenate them line by line using VB script. Sounds stupid and slow, but WHATEVER THAT WORKS!
Ok try out now! I struggled with the bloody syntax - it's similar to Excel macros but the summation button is the chimmest thing I can do on Excel >_< And it did work!!! Tried with lots of data, not slow also! Finally!!
Emboldened by my new bag of tricks, I threw in decoration for the headers. Just bold the header only lah. Something that wasn't mentioned in the specifications, but I knew the users would surely ask for.
Breezed through SIT by giving the files generated from UAT. Again, TL asked me to deploy to SIT, but I called it a waste of time. Attitude liao haha.
For UAT, I wanted to give the users the same files for them to verify, but they asked for the data from Production. Hey... that means I need to deploy to Production although we haven't passed UAT? Where got like that one? NO!! Block! Why was AiHiss supporting such a request anyway?
Actually, I was in favor of it lah, because I'm not so comfortable with the unconventional way the batch job was done. Testing with real-life data on the actual server will allow me to prevent failures upon rollout. We have been having issues for every release and I dread the day I'm responsible for one!
Also, the batch job would not affect anything else if deployed to production.
But the PM succumbed. O well... Now I had to deal with the DBA because I'm gonna mess with his server... and he didn't know SSIS as well as I did yet.
Surrrprise! Gargantuan volume of data for No Use Hospital, couldn't fit into 1 Excel worksheet at all! Each worksheet could only hold 216=65,536 rows but that was not even enough for 1 week's data.
See, never do impact analysis! How? Need to send out the files tonight already.
Don't care, I shall take the liberty to split into more Excel files. Got to do it manually since I needed time to figure out how to automate it. Actually, I'm not going to entrap myself by suggesting that. Splitting into different worksheets in the same file was a no-no too. Any form of splitting is bad! Probably shall propose increasing the job frequency or just spool into flat file (dream come true!).
Surrrprise #2! No other institution's data volume even came close. Only Took Their Savings Hospital might have a problem if they suddenly decide to increase their volume and of course they won't think they were obliged to warn us. How to convince them to accept my proposals above?
I don't know. The TL and PM can think about it. I'm going home, bye.
So in the end, No Use Hospital agreed to the flat file with the job frequency unchanged. because the user is savvy enough to import into his own database. But the rest of the pampered guys still wanted Excel, but job frequency increased to weekly.
Great, now I have more things to maintain!! ARGH!!! STAB ME PLS!!!
Since things were going to be kept within a single worksheet, we could merge all 3 files into 3 worksheets in a single file. Less clutter!
The batch job entered production officially. I had to manually generate April's files :( While doing so, I found that sometimes, the batch job simply refused to start, throwing some weird Excel error again. I found numerous Excel processes still running although there were no active applications. If I killed the processes, the batch job was back to normal. Odd... Shall see if it happened when it ran automatically.
Yeah... It happened. Luckily I anticipated it and was able to recover the files before the users found out XD
And so began the long and painful investigation and fixing. I swear I have .Close and .Quit each and every Excel object I created. Want to give up, but... :((
Can I just kill every Excel process I find?
Sure, if I'm quitting really soon, like, now!
As it I didn't have enough problems, my SQL Server Management Studio expired. Reinstalled, but now my Visual Studio Tools for Applications was unable to find my imports. Hey wtf, it was all ok just now! After fumbling through tonnes of websites and forums (many with why-are-you-asking-a-noob-question-pls-go-read-up kind of reply), I found that some system files have gone missing. No idea how it happened. Together with the DBA, we copied them from whereever they were found, to the GAC folder. And you can bet I have never heard of GAC till today.
The discovery - in the VB script, each time an Excel element is manipulated, an Excel process will be created. Each worksheet, row, column, blah blah, right down to the cell level. And they will linger until you release each them explicitly in the correct order.
OMFG why does something like this still exist?? 我要上吊!!
FINE. I FIX. *grumbles*
And the batch job was finally running successfully on its own.
But it's not the end of the saga yet.
Disk space in the server was running out fast with this new report! I never developed for archival - I thought of it, but usually not part of the batch job mah, and basic requirements not done yet, so this can wait! Aiyah zipping is also a temporary solution only. Users want them in the shared drive mah, so the only true solution is to increase storage. Out of my hands!
Monitoring guide not done yet. LATER!!
Coming up next, more problems.
No comments:
Post a Comment