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!

Advertisements

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.

First Post!

I’m going to start off with a little something I use at work to keep track of the requests my team and I are working on.

Based on the ‘Kanban’ priciples, I use one of my cubicle windows as a board with sticky notes to  easily represent what is going on with my team. Below is a snapshot of my board.

20151013_161312680_iOS

The way I have it set up, There is color-coding for the different departments we support. Working from Right-to-Left (I only do this because of the location of the wall, i’d rather go left to right):

  • Ideas: Thoughts to use later but don’t have time at the moment. Sometimes a random thought will be of benefit to someone else.
  • To-Do: Requests that will need to be done. Typically, they’re in the To-Do until the priority moves or resources become available.
  • In Progress: What the team is working on “Right Now”. I try to group by priority/assigned/department depending on how things are moving.
  • Testing: Anything that is out of our hands and needs to be approved to be moved to production or back into the in progress queue.
  • Complete: The request is done! It’s nice to see progress with everything going on.

20151013_165536259_iOS

How the note is laid out:

  • The upper left highlights the Department the request is for.
  • The upper right displays the request number. I use this number to reference any documentation written up as well as a secondary list of requests to the team.
  • The center boasts a short but meaningful title so I can quickly recall what it is about.
  • The bottom right is usually the initials of the person assigned to the request.

For as small as my team is and the relatively low complexity of the requests we get, I’ve found this method to work really well. It has evolved and will continue to do so as time goes on. I personally prefer it to the endless spreadsheet solutions that I’ve encountered from others around me, which brings up an interesting benefit. Since this is not the norm and highly visible, it gets people talking and in some cases inspires others to get more organized. This is nice, since it’s a part of my job to improve processes!

Just search the web for Kanban and see how it works.