SSIS: Why You Should Use Temporary Tables

There seems to be some debate about the use of temporary tables in SSIS. I, for one, highly recommend using temporary tables rather than trying to pull data from different sources straight into your database production tables. Consider the following:

  • You are pulling data across a VPN connection when the connection suddenly fails. Perhaps a router failed or something, but now you are stuck with a partial pull.
  • There is some type of unexpected corrupt data that causes your flow to error out. Again, you are stuck with a partial pull.

In the above situations you’ll now be stuck with production tables in a production database that have records that need to be cleaned out. At best, you’ll have to figure out which records made it and which didn’t and then make up the difference. By using temporary tables in your production database all you have to do is wipe out the temporary tables and then start over after figuring out what the error is – the idea being that once the data makes it into your temporary tables you now have full control and no longer have to worry about VPN’s going out or data needing to be changed or cleaned as all that has already been done.

So in your SSIS package you pull data over to your database into a series of temporary tables that might in face have the same structure as your production tables just with different names. Then you pull the data from your temporary tables into your production tables. Some even go as far as to have a separate database just for temporary tables which is perfectly fine.

Remember, when pulling data from other sources, especially outside sources, unless you have some ownership over said sources you control only half of the operation. You have no guarantees as to whether the source is reliable or the data is clean. Therefore, it is very likely your dataflow may be interrupted.

See my post on handling temporary tables in Access.

JamesNT

Posted in SSIS | Leave a comment

Revisiting old times

I was going through my closet in my home office over the holidays doing some cleaning and ran across these gems. I have to admit, I miss these days.

 

JamesNT

Posted in General Commentary | 1 Comment

MSExchangeIS MailboxMessages Queued for Submission is not making progress – Exchange 2010

If you get reports from users that their emails are stuck in either the Drafts or Sent Items box and are not being sent, you should run the Microsoft Exchange Troubleshooting Assistant. If you get the following error message from the assistant, check the free space of the drive that the Exchange Transport Role is installed on to ensure it has at least 2.5GB of free space.

Once you free up space, go to SERVICES.MSC and restart the Microsoft Exchange Transport Service. Email should start working again.

Note: If you receive this error on a SBS 2011 box, one of the best ways to free up space is to move the WSUS database to another drive. Instructions for how to do this for SBS 2011 can be found here.

JamesNT

Posted in Exchange | Leave a comment

Older Emails Missing After Upgrade to Outlook 2013

If you have upgraded to Outlook 2013 from 2010 or earlier, you may notice that emails from more than 12 months ago may be missing. If that is the case, you should read the following article:

http://support.microsoft.com/kb/2733062

JamesNT

Posted in General Commentary | Leave a comment

The Dangers of Giving Vendors too much Power

Back in the 90′s I worked for Sonoco. The capital project I was on was to install a new maintenance management software in an entire division of the company in order to, mostly, bring down the cost of spare parts inventory. During this project, many ideas for costs savings came up. One of them was that vendor reps should maintain parts usage history and stock the parts we bought for them in the parts room for us. I voice my concerns but since I was just a kid in my 20′s at the time with no college degree, I was promptly ignored and told to shut up.

This turned out to be a monumentally horrible idea as I predicted. Here’s why:

  • Let’s assume your vendor is a good guy. He’s only out for the benefit of you, the client and he wants to keep you as a client. Well, obviously, the last thing he wants is for you to run out of parts otherwise your machines will go down and you’ll yell at him. So the vendor may decide that, just to be on the safe side, he may need to stock a few extra bearings here and a few extra sprockets there because “you never know when they might have a spike in parts usage and we need to be careful and plan for those things.”
  • Let’s assume your vendor is a bad guy. He’s about $600 behind on his sales quota for the month and right now he’s not necessarily out to benefit you. So the vendor may decide that, since he’s the one stocking your parts room and you’ll most likely never notice, he may need to stock a few extra bearings here and a few extra sprockets there.

This is the danger of giving vendors too much power over your stuff. It doesn’t matter whether the vendor is a good guy or a bad guy, you’re screwed either way.

JamesNT

Posted in General Commentary | Leave a comment

My Second Thoughts on the Retirement of TechNet

Previously, I wrote about the retirement of the TechNet Subscription and voiced some disagreement about Microsoft’s decision. However, I have begun to rethink my disagreement because of two reasons:

  • There really was a lot of piracy out there.

In thinking back over the years to all the different small offices I’ve been in – offices running Windows XP, Office, SBS 2003, etc. – I begin to remember all the times I would ask people where the license keys for their software was and how I could never get a straight answer. Or no one knew at all. I’ve been in some offices where I knew for a fact that the software came from a TechNet subscription – literally thousands of dollars of MS software installed on production equipment. And those are the ones I know about. But what about all the others where, when I asked for CD keys, shoulders were just shrugged? $400 a year for all that software is pretty tempting.

Microsoft doesn’t like the little mom-and-pop IT Pro or the individual who owns his own business. In fact, calling these people “IT Pro” gives them way too much credit. I cannot begin to count the times I have seen utterly hosed Small Business Server installations. Or backups of virtual machines that don’t work because the person who set up the virtual machines didn’t leave enough space for the volume shadow copy. And, of course, the person would not know how to solve that problem. I cannot begin to count the servers I’ve seen that had all kinds of anti-spyware and other utilities installed all over them – completely useless utilities. And, of course, when you question the “IT Pro” as to what kind of problems they are having, the first thing they do is BLAME MICROSOFT FOR EVERYTHING all the while they are racking up billable hours for the client. While this sounds great for the “IT Pro” in question, MS has been for quite some time getting tired of all the blame. And don’t get me started on the small DIY office that doesn’t even have an “IT Pro” on hand – employee or contract.

These small IT Pros are also the ones who are abusing the TechNet Subscription the most. They install $800 copies of Windows Server everywhere, bill the client for the full cost of the software, and have a mere $400/year tied up in expenses. And, I dare say, these small IT Pros are also the ones complaining the most about the retirement of TechNet – they are losing a very valuable revenue stream.

Microsoft has responded in what I consider to be the best way they can. Keep in mind that some of the stuff listed below I did mention in my previous post. Yes, I am backtracking. But then again, things like the retirement of the TechNet Subscription are what happens when you bite the hand that feeds you. Also, do note that some extra options have been added that MS has pointed out on their TechNet page since my last post:

  1. TechNet Evaluation Center: This option allows anyone to download free versions of MS software that is time-bombed for either 30, 90, or 180 days depending on the software. For small IT shops or individuals, I would consider this to be a viable option especially now that MS has added older versions of software to the list. I’m an IT Pro and I can certainly go through Mark Minasi’s books Windows Server 2008 and Windows Server 2008 R2 with probably one or two installs. Testing some client software for compatibility might require another reinstall or two, but again, no big deal. I would do this all in VM’s so the one real inconvenience would be backing up VM’s and reinstalling the host (or maybe I should just use Hyper-V Server.) This obviously isn’t a perfect solution and I would expect to waste a certain amount of time reinstalling and adding patches/service packs, but it’s not the end of the world, either. I do, however, recommend that Microsoft extend any software that is time-bombed for 30 days out to 90 days. Thirty days is just too short.
  2. TechNet Virtual Labs: My original opinion of this option stands. Great for the 5 cent tour, but not for deep dives.
  3. MSDN Subscriptions: To be honest, some IT Pros should consider this option. In fact, a lot should consider this option. For many of the small shops, as the cloud begins to take hold they are going to start finding themselves squeezed out. That’s all there is to it. Moving over to software development, including Azure and mobile, would be a great way to maintain their jobs. Moving over to software development of some sort might be the natural order for the IT Pro who finds the demand for his current skillset going down. Remember back in the 80′s and 90′s when all those tobacco farmers starting losing their farms because the tobacco industry started getting sued and the demand for tobacco went down? Have you noticed how all those wineries suddenly popped up in your area that weren’t there 20-30 years ago? Might be a connection there.
  4. Microsoft Partner Network: By becoming a registered Microsoft partner, you gain access to the Action Pack. The Action Pack is a lot like the TechNet subscription (and even costs about the same) only you can use the software in production (there are some other caveats as well). For the small IT Pro that has enough clients, this might truly be the way to go.

Of course, what about those of us who need to migrate clients off of Windows XP/Server 2003 and we need installation copies for testing? Microsoft did extend the TechNet Subscription for 90 days and you did have until the end of August to renew one last time. That means you have 1 year and 3 months of your subscription remaining that will allow you access to all that older software. I highly recommend beginning your migrations now. Big companies that have SA don’t care about the TechNet subscription as they have other agreements for testing in place.

In conclusion, what Microsoft is doing is very simple. They are trying to stop piracy and get rid of the small IT Pros who are a big chunk of their support call costs. Does that mean moving small business to the cloud? Perhaps so.

I’m not saying all the small IT Pros out there are bad, but I am saying too many of them are. And the good ones, like me, know that to be true.

JamesNT

Posted in General Commentary | 1 Comment

The Retirement of the TechNet Subscription

It’s been April since I’ve posted to my blog. My apologies. It’s time to get back on the ball. I’d like to start with my opinion on the recent announcement by MS to retire the TechNet Subscription.

Over the past few weeks, the web has been abuzz about Microsoft’s announcement to retire the venerable TechNet subscription. I, myself, have been a TechNet subscriber for almost a decade.

For those not in the know, a TechNet subscription provides the subscribing person copies of almost all Microsoft software for a few hundred bucks a year. That’s right, a year. The copies are not time-bombed at all. These are fully functioning copies of Windows, Office, Exchange, SQL Server, you name it just like the ones you get from the store or your reseller. The ONLY difference is the license agreement from TechNet states that the copy can be used for testing and evaluations only – NOT PRODUCTION.

As replacements for the TechNet subscription, MS offers the following:

  • TechNet evaluation center. This is evaluation copies of all MS software but are time-bombed for 30 to 180 days depending on the software. For me, and many others, this is unacceptable as testing can be long term. Also, it can be time-consuming to set up complex environments. Has anyone installed Windows 7 lately? Notice how many patches you have to download? So what if you need an environment with four Windows Server 2008 servers, a dozen workstations, Exchange 2010, and a SQL 2008 Server? Throw some apps like Office 2010 in there and you’re going to spend a decent amount of time just getting patches installed – never mind getting everything configured (DNS, DHCP, whatever roles you need on Windows, etc.). Keep in mind your workstations are going to bomb on you every 30 days thanks to MS Office and you’re going to redo those servers every 180 days. I would like to point out that at one time TechNet subscription software was all time-bombed as well with the same 180 day limits. That’s right. You paid ~$400 per year for time-bombed software to be delivered via CD to your office. I remember MS advertising that the software would no longer be time-bombed and how excited everyone was. And the reason MS gave for the software no longer being time-bombed was the same we are discussing now – so people could set up complex labs with long run times.
  • TechNet Virtual labs. As the description suggests, these are designed to be completed in 90 minutes or less for the given technology you are checking out. These are great for basic self-training on various softwares, but not for deep-dive stuff where you are really getting into the thick of things.
  • MSDN Subscriptions: You can get most of the software via MSDN but we are talking a subscription of several thousand dollars. If you have no developers on your staff, then that means you are stuck with dev tools you don’t need. This idea would work, but at a much higher cost to the person who is promoting MS software at the benefit of MS.

One thing I find very interesting is how many of the software pundits out there simply shrug off the retirement of the TechNet subscription as not a big deal since everything is moving to the cloud anyway. I find this ludicrous. First, not everything is moving to the cloud. Organizations and large companies, contrary to popular belief, are not ripping up all their infrastructure in a mad rush to the cloud. Companies such as Corning, General Motors, etc. have systems in place that are years if not decades old. These are systems that are well debugged whose behaviors are well understood and took a great deal of time and money to set up. Ripping such systems apart and moving them to the cloud simply isn’t on the table. Even many of the Exchange servers out there aren’t going anywhere. Many companies have custom plug-ins to Exchange they wrote themselves that their company has since become dependent on that will not be supported in the cloud. To make things more interesting, the cost benefit of the cloud simply isn’t there after a potential customer company considers vendor lock-in (ever try to get your stuff out of a cloud vendor’s hands?) and what it takes to get the cloud vendor to guarantee a certain amount of uptime. Lastly, most companies aren’t moving their infrastructures to a third party, they are rather reinventing their own infrastructures to be more cloud like. This means companies like GM, GE, Corning, etc. are keeping their datacenters and turning them into their own PRIVATE CLOUD. Obviously, people need to be trained on that kind of stuff and they need test labs. So that means they need TECHNET SUBSCRIPTIONS. And even the third party cloud providers themselves need TechNet so they can experiment on how to set up a hosting environment before they go live with an offering people are going to trust the life-blood of their companies to.

I realize why MS most likely retired the TechNet Subscription. I have been into way to many offices where most – if not all – of the software running in production came from a TechNet subscription. Some people simply took the easy way out. That’s all there is to it. But MS shouldn’t punish those of us who have been honest with them, have promoted its wares, and supported its offerings (READ: MADE A LOT OF MONEY OFF OF US) because of these rotten eggs. Therefore, I think MS should reconsider retirement of the TechNet Subscription. There are other ways to recoup the cost of piracy or at least mitigate it. I hope MS sees this. It is my belief that the TechNet Subscription makes far more money for MS than it costs it.

JamesNT

Posted in General Commentary | 1 Comment

My Thoughts on BYOD

Bring Your Own Device (BYOD) is one of those new trends I have seen come up in the IT world over the past 2 years.  Basically, the idea is that instead of the company you work for buying you the equipment you need, you just bring your own or you and the company go in half-and-half or something.  Companies like BYOD because it saves them money in equipment.  Why buy an employee an iPad when that employee can just bring their own? 

Like everything else, however, there are some caveats that need to be taken into consideration.  Let’s look at some examples:

  • The employee is using their own laptop for work.  However, their work requires some specialty software so the employer installs this software on the employee’s laptop thereby consuming a user license for that software.  The employee is terminated and now that software must be removed from the laptop.  Because the laptop is the personal property of the employee and that employee has all her tax information and so forth on there, the employee refuses access to the laptop for removal of the software by the company’s IT support.  How does the company reclaim the license?
  • An employee has been using his iPad at the office for months now.  He turns in his two week notice and the current employer finds out the employee is leaving to go work for a competitor.  How can the company search the personal iPad of the employee to determine if he is carrying any proprietary information out the door with him?
  • An employee is using his Lumina 920 Windows Phone to field sales calls.  The employee leaves your company for a competitor that offers more money.  And you now realize that all of your clients have only that employee’s cell phone number as the primary contact for your company.  Since the phone does not belong to the company, there is no way to seize the phone number.
  • An employee has been using his laptop for work for four years now.  You know that employee has lots of company data on that laptop.  One day, the employee comes to work with a brand-new Dell Latitude laptop.  What happened to the old one with all that data on it?  Was the hard drive properly wiped using a D. O. D. compliant shredding software?  Did he just give it to this kids without cleaning off all that proprietary data?

In all the above cases, the company is at a loss.  It is difficult to just search an employee’s personal property without some type of privacy violation which could lead to bigger problems.  Furthermore, an employee who uses their own laptop/computer/smartphone/other device may not consider security at all since the employee views the device as their personal property and not as a vessel of corporate property.  How are security updates getting done, if at all?  What if the employee is allowing friends or other family to use the device to play games or for their own use?

BYOD can be a real money saver in regards to having to purchase equipment.  However, as always the long term costs must also be considered.  Devices that are not under the control of the company yet contain company information can pose true security issues and place the company in a bad situation regarding privacy.  In my opinion, BYOD is a bad idea for most organizations – especially those that are governed by some type of federal regulation such as HIPAA.

Before your company proceeds forward with a BYOD policy, you may wish to consult a lawyer.

JamesNT

Posted in General Commentary | Leave a comment

WIMFSF.SYS PAGE-FAULT_IN_NONPAGED_AREA

If you get this blue screen during installation of Windows, one of the most likely causes is bad media.  Replace your DVD or download a new ISO image.

If you get this error installing Windows 7 in a VMWare Workstation virtual machine, VMWare has that “easy install” feature were you can type in the product key in VMWare’s set up screen before installation of Windows starts (as well as specify username and password).  Make double sure you have a legitimate product key.

image

JamesNT

Posted in Windows Client and Server | Leave a comment

Handling Truncation in SSIS

Truncation is what happens when you try to fit a string of one length into a field that holds a string of a shorter length.  In most cases the last characters of your string are cut off so it will fit in the field.  Of course, in the case of SSIS your package will error to a grinding halt if truncation is not explicitly handled.  We see truncation very often in fields such as first name, last name, address, and so forth when moving data from one platform to another.  While there are many ways to handle truncation, I’m going to discuss some of the more common I’ve seen here along with their plusses and minuses.

Approach One:  Use Substring() ,or equivalent function, in your SQL statement to pull out only as many characters as you can handle in the destination.  In the example below, we are using the Mid() function since we are pulling from an Access 2000 database.  The FirstName and LastName fields in the Access database are of length 20 but our destination has those same fields of length 10. 

The pros of this approach is that it is quick and easy and keeps your overall dataflow clean.  You don’t have to worry about getting possible truncation errors during different steps of your dataflow.  All of the “mess” is contained in that one nice and neat little SQL statement. 

The cons of the approach are that you will lose data.  You are forcibly truncating strings, after all.  If someone has a long last name, that name is going to come over with the last few letters missing. 

image

Approach Two:  Use a Data Conversion Task to shorten your strings en route to the destination.  You can either ignore the truncation by configuring so in the Error Output, or you can redirect strings that would be truncated to another table. 

The pros of this approach is it gives you the opportunity to redirect any rows that will be truncated to another table or flat-file for later analysis.  This may be very important for some data where you can’t lose anything.

The con of this approach is it makes your data flow a bit messier.  You will have now the “copy of firstname” and “copy of lastname” columns as shown in the screenshot below and the original firstname and lastname columns.  You can remove unwanted columns by adding a Sort task and not letting them pass through, but again, it’s a bit messy.

image

image

Approach Three:  Attempt to use a script component to shorten the data.  Consider the  following code:

public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        if (Row.AddressOne.Contains("Avenue"))
        {
            Row.AddressOne.Replace("Avenue", "Ave");
        }
        if (Row.AddressTwo.Contains("Suite"))
        {
            Row.AddressTwo.Replace("Suite", "Ste");
        }
    }

Notice that we are attempting to replace certain words commonly found in addresses with their abbreviations.  This approach is commonly used with approach two so as rows get redirected, more words can be found to be abbreviated thereby cutting down on error.

The pros of this approach is that it can certainly cut down on truncation in a more acceptable way.

The cons of this approach is that it will not work for all data and it can be hard to maintain as you are constantly adding cases to your switch statement or more “if’s” in the case of this example.

Truncation is one of those things that you are guaranteed to deal with when working with data.  Some vendors are certainly more generous with space in their database than others.  Why do some vendors make some string fields so small?  Who knows.  But at least we do have a few options on how to deal with it.  Maybe not perfect options, but options nonetheless.

If anyone has any cool approaches to how they handled truncation, I look forward to reading about them in the comments section or via links to your own blogs.

JamesNT

Posted in SQL Server, SSIS | 3 Comments