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

Going from “We can?” to “We can!”

It’s an awesome feeling when you can give someone an AHA! moment. When you can provide the solution to a long-standing issue, it can be very satisfying.

“You can do that?”

“Yes.”

“Yes! We can do that!”

The journey to get to that moment can be a long one, with many struggles along the way. Sometimes it’s just a matter of knowing what is available and who to go to in order to make it happen. My team experiences this a lot. In a fairly young/new/small environment, some formal processes haven’t been cemented yet. Knowing who does what or the ownership of data/processes can be a mystery at times. You’d think that in a small, fast moving environment, the communication and collaboration would flow freely.

Not so much.

That’s where my team comes in. We provide end-to-end support for the Core services, as well as the connected software and services. Additionally, we provide:

  • Automation
  • Optimization
  • Reporting / Analytics

About 2 years ago, this group was formed with just one developer and myself. Since that time, we’ve more than tripled in size, including business analytics and print fulfillment. Working with our internal customers, we work out processes and create solutions for their various needs.

For example (a fraction of what we do):

  • Instead of multiple downloads of the same file
    • Setup a process to deliver one file in a shared location and send a notification.
  • Instead of manual processes, use automated tasks to complete repeatable functions.
  • Use SSIS and SSRS to provide self-service and automated reporting to replace spreadsheets and querying.

From the beginning, the goal was to provide solutions and ideas to make their work day easier. Word gets around and the requests increase.

“Hey, I heard you can…”

Yes, yes we can.

Office 365 – OneNote: From phone to collaboration bliss

I’m not one who usually geeks out on new tech as it seems to me most ‘advances’ are minor mods that aren’t all that exciting or do me any good. I do like to tinker with different tech to see if it will help speed up development or collaboration with my team. You never know when you’ll come across something that helps.

We employ the Office 365 suite at work, though not as thoroughly as I believe we could. Given that, I take any opportunity to make use of it. From SharePoint online for organizing  important documents, schedules and other resources for my team to the new ‘Planner’ software recently rolled out for task tracking. One really neat benefit in O365 that I found is the cloud aspect of connecting data and applications easily.

Scenario: Multi-team collaboration on a whiteboard.

Typically, we would work something out on the whiteboard and either hand write the notes, type it out or take pictures. Afterwards, go back to our desks and email around with our notes and try to sync them up. Usually, we can agree on the output. If there are any pictures taken, they have to be emailed or downloaded from a cloud backup….Lots of extra steps.

NOW: Office 365 and OneNote

This definitely speeds things up and reduces any questions as to accuracy.

I downloaded the OneNote app for my Android phone (I believe there are versions for other platforms as well) and logged into my O365 account for work. I created a new page to work with (fig 1).

1note_1 Fig. 1: creating a new OneNote Section.

Once in the section, you can see the new section and how it relates to any other notes you have.

1note_31note_2

In the upload example section, click on the camera icon to add any related images that you need to share.

1note_4

Once you have you images, you can add text and name the page for the section.

1note_5

This is my favorite part – This page can be viewed from the web-based OneNote or your desktop version (if connected).

1note_online  Online

 

1note_desktop  Desktop

Now, anyone who is connected to that OneNote can collaborate on the Notes taken – Single source of information!

Hope you found this helpful – Enjoy!

TG

Log file from Excel COM API program

It’s a good habit to track the data your process has changed. Most programs should have some type of logging mechanism. Sometimes in the haste to roll a tool out the door, simple tracking pieces are left off. I ran across that recently when working with some of our Macro-enabled spreadsheets. Using the COM APIs provided with the software we use for our billing system, we’ve built some tools to process data in a different fashion than the standard jobs from the system.

I noticed that there wasn’t any output from these tools though. That bothered me when updating numerous records that may have needed to been rolled back. Doing some digging online, I made some mods to the VBA coding to allow for a ‘log’ file and track what the code did.

For this entry, here is the setup: An excel spreadsheet with a button that will run some VBA script. There is one tab that has the data points to process and the button to fire off the code. The other tab is for configuration: log output directory, username, etc. This is a rudimentary way to set up where the file goes and how to name it.

xl_output_tabs

There will be two important variables:

  • “Operator” (who is running the process)
  • “FilePath” (where the log file will be written)

xl_output_tabs1

The key with these fields is to name [B1 and B2 in the pic] appropriately. These will be used in the code. In the example above, click on B1, then click on the namebox – Type the name of the cell (like FilePath) and hit Enter. This aliases the cell where you will put the output path of the log.

SET UP

In the code editor (Developer -> Visual Basic), add the following to the code in order to create and write to the log:

xl_output_vbacode1

Using some variables, capture the configuration data before going on to the rest of the code. The vFileName combines the configuration operator value with a date stamp and file name to distinguish the function of the process. It uses the cell aliases from the config tab.

Creating the log file

xl_output_vbacode2

This creates the file using the filepath variable and filename using some built-in VBA code.

Writing to the log

xl_output_vbacode3

In this case, I wrote the data to a variable first then used the VBA API to write to the log using the variable. Use this at any point in the code to log an action on the data. In the example above, I’m logging that I updated one value to another for a particular account.

Output Result:

Based on the config, I can check my output.

xl_output1

Opening on the file I know I just created, I can review the details of the process.

xl_output2

Conclusion

That’s about it! A pretty simple solution to help keep track on changes made to data via the VBA processing.

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.

 

SSIS to Excel output limit fix

With many of the SSIS packages my team writes, we have to output to an Excel Destination. One problem we found is that any data sets over 10k rows would not populate the Excel file all the way. We tried everything from buffer size to exporting to text first – nothing quite worked!

After much research, we tested a theory. Our Network admin modified permissions on the output directory and the account that was running the SSIS and generating the Excel files. I believe it had to have full admin rights on the job submission directory.

Problem solved!

Seems rather odd that because of permissions, the excel destination is limited. We could tell from testing. Limiting the output of the same query to say, 100 records was no issue. 9999 records – no problem. As soon as the limits were off the problem returned. That’s where we figured there was some type of limitation problem.

A couple of other points to the process:

  • We have a generic Batch account to run the processes from the SQL agent, instead of having one of the team member accounts use it. This helps to maintain a single point of permission management.
  • We also have a single output directory on our sharedrive. The ‘job submission’ branches off for each department and individual outputs beneath. The structure is locked down to read-only but users can copy off for themselves and modify the file to their hearts content. One too many times the original file was messed up or deleted.

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!