Thursday, May 13, 2010

More Software Emergency Fixing Experiences

I've recently helped two more clients with 'emergency fix' issues.

The first issue was a problem a national grocery chain had with an export from a SAS program that did a pull from multiple database tables into an excel spreadsheet, and subsequently imported this data into a MS Access database using a SSIS (Sql Server Integration Services) package.  The SAS export seemed to be fine - that is, it executed without errors, but the SSIS package would fail. From my understanding, the client would run this process about every month or so, so there were a fair number of records (1000's).  However, they started having this issue in Dec. and I didnt' get called about the issue until late April, so there was a backup of data.
The client didn't have the SQL BI studio installed, so we had to go through the idx file manually to see if there was anything obviously wrong.  There didn't appear to be.  The database and excel connections tested fine. There didn't appear to be anything riotously wrong with the data formatting or invalid characters in the excel spreadsheet.  So, what do you do?
Well,  for one thing, don't test with the full dataset if you think it might be a data related issue.  Keep making your dataset smaller until you are sure that none of the data works.  In this case, we cut the dataset in half, started from Feb to the end of Apr and the import worked!  This confirmed for us that there were no permissions issues, or software upgrade issues (we were considering that software upgrades might be part of the problem as more than one piece of the software had been upgraded in that time period).  The successful test confirmed for us that we were dealing with a data related issue.  From there it was just a matter of determining exactly which rows of data couldn't be imported, and what it was about the data in those rows that botched things.
Less than 5 hours of work to this point on this fix.

With the second emergency, a telecom company was having issues getting an automated FTP (file transfer protocol) process to work.  They had changed the paths of where the ftp files were residing and they were voip experts, not so much into the bash shell scripting thing.  Their first problem was pathing in the bash file.  That was an easy fix.  Then there was a problem with sending a notification email that apparently they never got to work.  This was a little bit more tricky.  They originally had the email coded similar to this example.

       telnet 25 <<_EOF
       HELO       MAIL FROM:<>
       RCPT TO:<>
       DATA       From: Joe <>
       To: Jane <>
       Subject: Hello
       Hello, world!
       QUIT       _EOF

However, after a bit of work I discovered that the server this script was running on was processing all the command too quickly for the smtp server to get the email off.  In the end, I had to do something more like this:
       ( echo HELO       
       sleep 2
       echo MAIL FROM:<>
       sleep 2
       echo RCPT TO:<>
       sleep 2
       echo DATA
       sleep 2
       echo Subject:Test-Mail!
       sleep 2
       echo If you can read this, it Works!!!
       sleep 2
       echo .
       sleep 2
       echo QUIT
       ) | telnet XXX.XXX.XXX.XXX  25

Then to set the bash script to run in a windows scheduler (using cygwin) I had to do something like this:
       C:\cygwin\bin\bash.exe --login -c "/myfolder/" 
Less than 5 hours to fix.