It’s Elementary!

Some recent conversations reminded me of an interesting problem my team had to resolve. Through some ingenuity, business process analysis and a little bit of grit we were able to find the issue that almost ground admissions at this particular site to a halt.

It was about a week after I started working with this client. I was working with the team of programmers when the complaints started flowing in. Approximately 50% of the students enrolling in courses that term were not getting their generated email IDs or system accounts.

Problem Identification

The initial review of all the workflows and systems showed no obvious issues. At a high-level, the student goes through the admissions process and then once accepted, has their data pushed through a semi-automated process that creates their accounts. As previously stated, a lot of those students weren’t getting what they needed. No recent software updates, no system changes – we were stumped.

Temp fix if needed

While the team dug in, we were able to implement a temporary ‘process’. One of the programmers could take student information and manually create the required accounts for students. The downside of this was that it took most of that persons’ time each day to process, not leaving much time for regular work. It got the job done and luckily it was only the summer session so it wouldn’t be as bad had it been registration for the fall.

The big question

“How can students get into the system?”  – Meaning – what are all the possible ways for data to get into the SIS? We knew the ERP software pretty well, so we knew that there were a few possibilities. After testing and ruling out the proper processes (these worked as expected), we focused on the non-traditional.  We noticed that in most of the cases of the issues, they were missing admissions applications. Note – this was a critical piece in the workflow which created the necessary accounts.

The Gathering

Given all the information we had, we decided that we needed input from the key players. During a meeting with Admissions and Registration, the truth was revealed. After the team presented our findings, it came to light that the Registrars office was skipping the admissions process and putting students straight into courses. By doing that, they bypassed the proper process components to kick off the workflow and create the accounts – hence the daily issues. Kind of humorous that two people who virtually sat next each other could do things so differently.

Win some, lose some

Now that we had the complete picture, we could implement a real solution. The most simple and straightforward path would be that the processes be followed and not skip steps.  Unfortunately, this is one of those times the executive level overruled the proposed solution. We had to develop a new workflow to accommodate the different student entry point in order to create the needed accounts.

In the end, the issue was resolved. Sometimes, that’s all that matters even if you are left scratching your head about it. Sometimes, you have to chicken-wire and duct tape things together…and that’s a lot of the fun of working in IT. There’s a lot of tech out there, the key is getting the right people and right tech to get the job done.

Cheers! TG

Advertisements

Certified!

Finally took my ITIL foundation test and passed it (33/40 correct). I’m really excited that I was able to succeed in passing it with a fairly good score. I’ve already been using the concepts of ITIL in my daily work, so this will solidify my application.

When I finally saw this, I was elated!

tgcert

Looking forward to my future endeavors on the ITIL path.

Thanks for reading – TG

SSIS Excel destination: Templates!

If you have a report that always outputs to an Excel Destination, here’s a trick my team uses to always export the output the same way each time. We use an excel template to keep the output uniform.

  • Run the output SQL first to get the data.
  • copy/paste the output to Excel.
  • Save the file with a generic, but related name. We keep our templates in a separate directory for safekeeping.

template_dir * directory

template_example * filename

  • Remove data, but keep headers.

template_ex_headers

  • Create 2 variables in SSIS
    • srcFile (source file / template)
    • dstFile (destination file, using expression for datestamp)
  • In the beginning of your SSIS package, use a filesystask and use the two previously created variables.
    • filesysvars
    • What this step does is copy your template with headers to a new file location with a unique name (based on datestamp).
  • In the Excel Destination step, create an expression in the properties.
    • exceldstexpr.PNG
    • This will write your output from your run into the unique file that was just created, without modifying the original template.

Using this technique, you can use the template over and over again without having to manipulate the output data after the fact.

 

2016!

Wow!

It’s been a while. Recently, my responsibilities at work tripled. This didn’t leave too much time to record any fun IT thoughts. I took a lot of notes though. More to come!

Visual Cron / Last Day of Month

The one scheduling software I work with is Visual Cron. This is a pretty good platform for organizing and timing the execution of different processes.

A new problem presented itself recently: Passing a specific date to a Windows batch file. For this particular program, the date had to be ‘last day of the month’ in which the program was running. Whether you’re going to try and find this date by SQL or Application functions, it’s not an easy task.

Visual cron allows you to create a custom date function, which I made use of to get the last day of this month. In order to obtain this value, the math basically states “Get the first day of next month, then subtract a day”.

In Visual Cron, create a User defined Variable [ LastDayofMonth ].

.vc_vars.PNG

The value for the variable is as follows:

{DATEADD(Days|{DATEADD(Months|{DATEFORMAT(yyyy-MM-01)}|yyyy-MM-dd|1|yyyy-MM-dd)}|yyyy-MM-dd|-1|yyyy-MM-dd)}

Using a day in November 2015 as an example:

  • This formats the date to YYYY-MM-DD (as needed by the batch file).
  • yyyy-mm-01 automatically sets to the first day of the current month [ 2015-11-01 ].
  • In order to get the first of next month, use a DATEADD of one month [ 2015-12-01 ].
  • DATEADD -1 day from that and you get the last day of this month [ 2015-11-30 ].

Now, use that variable in a scheduled process:

vc_vars2.PNG

In Visual Cron, as a part of the execute process flow, the variable (now converted to viscron-ese) is encapsulated by single-quotes to format the value that is needed in the bat file.

For testing, the .bat file is as follows:

echo %1>> lstdaymthvar.txt

After the process is run, the result is shown:

vc_vars3.PNG

I’m sure I can enhance the variable code, but for now, it works. 🙂

SSIS Variables

For a number of projects I work on, I make use of Microsoft’s Integration Services (SSIS) solution to run some queries and drop output into a formatted Excel file for our various end users. I’ve only been using SSIS for a little under a year, so I know there’s lots to learn.

One trick I’ve found to be very handy is the use of Variables in the the packages.  Specifically for this post, I’m focusing on using the expression to make the variable dynamic and using that value to output a file and change the name.

After starting the project, create a variable, making sure that the scope is for the package.
var1

Click the ellipses next to the line you want to create an expression for to bring up the Expression Builder.
var2

In the Expression text box, use the location of the output file (local or network) and concatenate with a date to keep it fairly unique.
var3

!!! One important item to note is that if your package works across the network, you will need to be wary of the escape character. When building the expression, you’ll need to double-up on the ‘\’ for each one used. For example, if you’re output directory is \output, the package will interpret it as “\o”. That’s bit me a few times before I figured out to use “\\output”. See the pic above to see what I mean.

Once you’ve decided on the value for the expression, click ‘OK’ and then the variable will use whatever the calculated value is.

In SSIS, I’ve typically used this for naming the products of the ‘Excel Output’ task [I will detail that in another post]. The variable can be used in the “File System Task” object as the ‘Destination Variable’ or ‘Source Variable’
var4
var5

If the path ever needs to change, you only have to update the variable.