Create a DTS package to Import Excel files

DTS stands for Data Transformation Services, and is the SQL Server 2000 forerunner to SSIS.  Among other things, it can be used for importing data from an Excel spreadsheet to a table in a SQL Server database.  That was what I needed to do, but you can import or export from and to pretty much anything.  

Before using the wizard to set up a DTS package, make sure that there are no existing versions e.g. for testing.  If a table already exists, the Wizard won’t put in a task to create the table, and DTS is fiddly to edit. 

Open SQL Server 2000 Enterprise Manager and navigate to the database you want.  Select the “Run a Wizard” task from the taskbar.  (It's the one that looks like a magic wand sprinkling stars...)

There are loads of wizards.  Select the Wizard you wish to use from the range available.  In this case we want to use the DTS Import wizard.

Select the data source type from the pull down menu – in this case Excel.

Browse to the source data file.  

If the source data is in Sharepoint, select My Network Places and choose the Portal.  

After choosing the file to be imported, click on Next.  

Now select the destination server and database.  If you forget to specify the database, the wizard will set up a table on the default database, which may not be what you intended.  In my case the default was Master.  So the package worked, and created a table, but I couldn’t see it anywhere because I was looking in the wrong place.  

You can copy the whole table, or use a query to select just the bits you want.  

In this example there are several worksheets in the spreadsheet, listed in the source column.  I have ticked All, and specified the name of the destination table. 

Click on Preview to make sure that you have the correct data. 

You can run the package immediately, or save it for later, or schedule it to run daily or weekly.  

.  If saving, specify the package name and optionally a description.  

Check the Wizard’s summary and click on Finish

This is what the wizard produces:

Right click on the Create Table task.  This allows you to edit the code used, e.g. to check whether the table already exists and if necessary, drop it before creating a new one.  

This sort of thing, perhaps:

-- ImportPortfolio Matrix

-- Jack Whittaker 02/06/2011

use SGSGROUP

go

-- Check for existence of table

if exists

(select * from dbo.sysobjects

where id = object_id(N'[dbo].[tblMasterCFCPortfolioMatrix]')

and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[tblMasterCFCPortfolioMatrix]

GO

-- create new table

 
If you forget this - and I did - then step one of the job will fail.  You can't create a new database if there is one there already.  But apart from that, it's not difficult at all.  

What I Did On My Hols

Yes, I know it's nothing to do with DBA Tasks, but you don't have to read it if you don't want to!

I went to the Eifel National Park.  No relation to the famous tower – two effs - but a landscape of steep wooded hills and lakes just over the Belgian border in Western Germany, about four hours drive east of Calais.  Imagine the Lake District with far more trees and far fewer tourists, and little medieval villages like Monschau full of half-timbered houses.  The warm sun shines on open air cafés serving beer with Wienerschnitzel (not every stereotype is false). 

Put on your walking boots or get on a bike and wander round the hills and lakes - this is what people do here.  Take a boat trip on the lake and walk back.  Cycle lanes and footpaths abound, making for lots of white paint at road junctions.  (Remember to wait for your pedestrian traffic light, even if it’s 3am and there’s nothing coming - the cops enforce jaywalking laws with 100 euro on the spot fines.  Ditto cyclists).  Once you get away from the main routes, a detailed map would be handy - I wasn’t impressed by the signposting on the footpaths.  Those of you adept at reading between the lines will realise that this means I got lost.  

The village is Gemünd, which is close to Schleiden.  There’s another Gemünd near Bitburg somewhere, and lots more places called Gemunden, so be careful programming the satnav.  Nearest big towns are Aachen to the north, Köln to the east. 

Accommodation is in a series of apartment blocks.  All the flats have a balcony I think,  but sadly although there are great views to east and especially west, our block faced south so we gazed at the closed shutters of the empty block next door.  Good sized living room with sofa bed, with galley kitchen and small verandah; smallish double bedroom; shower/wc. All fine for a week.  There’s one flat for sale with an asking price of 60,000 euros - you wouldn’t get a flat of this quality for that kind of money in the UK. 

What it doesn’t have on site is a restaurant or bar; nearest is the Salzberg 500m down the hill.  We found Aristotle’s rather smart Greek restaurant at the bottom of the footpath from the top of the complex down to the valley; a steep climb home...  Apart from that you need to  go down into Gemünd itself about 1.5 km away down a steep hill, where there are probably a dozen quiet bars and restaurants, so it’s probably not a resort suitable for party animals.  Although I admit I didn’t try the sauna.

Speaking of party animals having to make their own entertainment with their kit off - dominating the skyline to the west is the tower of Ordensburg Vogelsang (look it up on Wikipedia).  The National Socialists used to bring the best and brightest hunky blond young Aryan men to this training establishment and inducted them into a secret cult which involved them going into a secret room in the tower decorated with a big mural of the perfect German man. Who just happens to be naked.  You may wish to roll your eyes at this point.  After the war the place was used briefly by the British and then the Belgian army, who built a huge barracks block that Albert Speer would have admired, and replaced all the German eagles with Belgian heraldic lions. 

Off to Köln for the day, starting with a boat trip up the Rhine from the cathedral, commentary in German and English - oldest, longest, busiest, highest etc.  2000 years of history, check out the cathedral and the adjacent Roman museum, plus it has a chocolate museum, so pretty much the perfect weekend break.  Students of urban planning should come and see how to do it.  Cheap to park as well, 1€20 for an hour right by the cathedral. 

A month or two back I visited the Museum of Welsh Life in Cardiff (Amgueddfa Werin Cymru), and just down the road from Gemünd near Kommern is a very similar German version.  Park at the bottom and walk up the hill, and there among the trees are little clusters of half-timbered wattle and daub houses from up and down the Rhine, preserved as they were before the coming of the 18^th century..  They have a working wood-fired bakery where they bake the bread they sell on site.  Chickens and geese wander round the villages, but the pigs are confined to their sty which is probably just as well.  The occasional member of staff wanders round in historical costume, the effect spoilt only by the occasional squawk of the two way radio. They are building a new village covering the period from the 1950s to the 1970s, which makes me feel very old.  You can eat on site, but we went down the hill to Kommern, a living village of, er, half-timbered wattle and daub houses, and had a very nice Greek meal in the restaurant’s tiny shady courtyard.

Drove back to France via Maastricht in the Netherlands; lots of clothes shops for those that like them.  But the main square was blocked off as they were setting up stalls for a forthcoming antiques fair, so the pavement café atmosphere was spoiled for me.  I was surprised that the tourist information office actually charged for a map of the town; everyone else in Europe gives them away for free.  And the swish new Vrijthof underground car park beneath the main square charged more for an hour than Köln did for four.

Data Quality - What could possibly go wrong?

Well, pretty much everything.  If you have enough data, there will be mistakes in it.  Try writing your own name down on a piece of paper.  In CAPITALS.  There's quite a good chance that you will spell your own name wrong - something about the unfamiliar capitals trips up the link between your brain and your fingers.  Now imagine that you are a data entry clerk typing in hundreds of names every day.  Some of them are Arabic, others Polish, some even Welsh.  Can you guarantee that every name is correct?

There are ways to help, of course.  Take addresses, now - most countries have a postal code system which can be used in automatic sorting machinery to help your letter get to the right place.  Most countries use a 4 or 5 digit numeric code - 90210 famously defines Beverly Hills in California, so typing an easy number into your address software automatically fills in the town (and it isn't Beverley Hills) and state.  And by reading the relevant postal address file, the software can check the spelling of the streets.  The postcode systems in the UK and the Netherlands can go right down to street, even sometimes premises level.  But these things have to be kept up to date - and periodically the postal authorities make changes in order to take on board changes out in the wild, even sometimes to fix mistakes.

So your name and address data may contain errors - perhaps your older data even preceded the current computer system and was transcribed from a Rolodex.  Check it.

Check the names and genders and titles as well, while you are at it.  Where you find blanks, there are a few things you can do:

• Where there is a gender, you can derive a title (if Gender = F, then Title = Ms).  Note that while this is fairly risk-free for men, some women dislike being called Ms.  
• And vice versa - where there is a title, you can derive a gender (if Title = Mr, then Gender = M).  
• Where there is a forename, you can - usually - derive a title and gender (if Forename = Andrew, then Title = Mr and Gender = M).  This will still leave a number of names (e.g. Hilary) where it is not possible to determine the gender.  

Maybe you don't have a first name at all, just the initials.  Is that a man or a woman?  If you don't know, how can you address a letter?  Suppose it says Mr J Smith, but then states the gender as F?  Something wrong, but what?   You could of course classify such cases as "Unknown", but that might screw up your letters completely -  “Dear Unknown Smith” is not going to win you much business for your widget factory.  “Dear Customer” might be acceptable.

Suppose you have a list of email addresses - you need to make sure that they are valid before sending off your mailshot.  Lots of things you can check here:

• Check for spaces in field
• Check that name is present
• Check that address does not end in a full stop     
• Check that suffix e.g. .co.uk is not missing
• Check that suffix is not truncated
• Check that @ symbol is present     
• Check that @ symbol is not duplicated      
• Check that there is no spurious full stop in the address

I found some SQL code on the internet to do this - you don't expect me to do any actual WORK, do you? (After writing that sentence, the boss made me rewrite my code in PL/SQL in order to do that useful job for an Oracle database - I should have kept my mouth shut).  If you want a copy, let me know and I'll pass it on.  One big problem, of course - you can check all these things and have the most valid email address ever, but it's no good if your customer has changed to a new ISP last month.  

And while I think about it, don't forget that you need to comply with data protection legislation to hold all this stuff.  UK readers can find a handy checklist here, but there are similar rules for most countries.  You can get your data checked for people who have moved house, or people who have died (if you want to upset a bereaved relative, send a cheery letter to the recently deceased).  And if you know that someone has died, make sure that you don't contact them, especially not if you are going to write to Dear Mr Smith (Deceased).  

How to Keep Your Job

Emails, eh?  You reply to your friend with a sarcastic comment mocking some corporate announcement that Dilbert would have found extreme, and suddenly everyone in the company is reading your wit and wisdom.

The great thing about emails is that they provide a way to communicate instantly with lots of people.  The trouble with emails is that they provide a way to communicate instantly with lots of people.  In the days when you wrote a memo and put it in the internal mail, it probably wouldn't arrive until tomorrow, and you could always get it back from the out tray if you needed to stop it.  Or even if you thought of something to add after writing it.

Well, here's a handy hint - you can do exactly that with an email.  Remember I was talking about email the other day and how to set up rules to sort them into folders?  Well, I was.  You can set up a rule that slows down your email to a more human pace.  Let me show you how to do it.

Start by setting up a new rule.  In Outlook, click on Tools, and then select Rules and Alerts.

Right now, click on New Rule, and it'll give you a list of ready made rules, none of which do what you want to do.  That's to put a delay on outgoing mails, remember?  But right down at the bottom of the list there's a thing about starting from a blank rule, and the thing you are looking for - check messages after sending.  Select that one, and click on Next.

Right - now you get the following screen - lots of possible conditions.  You could think about setting it to put in a delay if you choose to send a message to Company - All, perhaps.  But you might still reply to a career-challenging individual by accident, or some weasel who forwards your message to the world.  Remember Claire Swire?  Yes, that Claire Swire.  So think twice, okay?  And apply this rule to all your outgoing emails - scroll right down to the very bottom of the list and choose "On this machine only".  Hit Next.

Again, there are lots of things you could do - but the thing we want is down at the bottom of the list again.  Choose "Defer Delivery by a number of minutes".  Then in the box at the bottom, click on the link where it says a number of  and type in 5.  If you don't realise that you have screwed up in five minutes, you probably aren't going to realise in 60.  Oh, go on then, put in 10, 15, whatever you want. See if I care.  

You could stop there, if you want.  Click on Finish, and go home.  Next time you press Send and then think "Ooops!", you can stop worrying - it's sitting in your out-box and you can grab it back, edit it, delete it, whatever you want.  I've saved your backside from corporate hell, and you owe me a beer, or suitable non-alcoholic refreshment if this is more appropriate in your jurisdiction.

Still here?  You want the last bit?  OK, suppose you really, really can't wait five minutes.  What a sad life you must live, to be sure.  Take a break, smell the flowers, gaze into the distance for a few minutes and let your thoughts drift away.  You'll feel better for it.

Right then.  Exceptions.  You really want it to go now, you can't wait five minutes because you have promised your boss to send something right now, and she is waiting, drumming her fingers on the desk and wondering what has gone wrong with the email system.  Before her blood pressure rises from a simmer to a rolling boil, I'll tell you.

Click on the thing that says "except if the body contains specific words".  Click on the link where it says specific words and type in your secret code for making things go straight away.

Like what?  You have to be a bit careful.  Don't type something like "Urgent" or "Priority" because you probably get lots of emails with words like that in them.  You can be dull, dull, dull and type something like "For immediate transmission".  I like to say "Priority Green" - it doesn't mean anything particularly, but you can stick it at the bottom of your signature file next to the corporate thingy about saving the planet by not printing out this email, where it doesn't look out of place.  If anyone notices it, they probably think it is some environmental initiative which they missed, and that you are obviously a very virtuous person.

Last of all, give your new rule a name, something like Delay Outgoing Emails, something like that and click on Finish.

From now on, any email you send has a life-saving delay built into it.  You can get round it if you really need to, but I shouldn't bother - five minutes won't usually be noticed.  The crucial thing is that you have put in a delay longer than the Ohnosecond.

Passwords

This is copied from an article by Buffalo Bill Brabant...

"There has been a resurgence of hackers  attacking webmail users and then using the stolen accounts to send ads and malware to the address books of the stolen account. This is at the least very embarrassing to those involved as one of the users of my Yard sale groups found out last week. After a post by a hacked AOL user they requested I remove the person's posting privileges. Instead I placed the user on moderation and explained what had happened.

As fate would have it, the complainer, who was also an AOL user got hacked about a week later.

Anyhow because it is timely here is an article I wrote about a year ago on password strength which is your only defense against being hacked.

We probably have all seen at least one of these lately, an email from someone we know to about ten of their friends with no subject line or the name of the person as a subject. When you open the email you find a url to a website and if you click on it and you are lucky the worst you will get is a Viagra ad but it just as easy for them to rig the site with malware downloads like antivirus 2010.

This started out a few years back when people were hacking Yahoo mails to steal choice addresses and after their methods were posted on youtube, spammers started hacking hotmail email accounts last winter and then more recently AOL, and within the past week Yahoo accounts.

There has also been a revival of a scam from last year where a person hacks into your email and changes passwords so you cannot check your email. They then send emails out to everyone in your address book saying that you were on vacation out of the country and that you have lost or been robbed of everything except your passport and need thousands of dollars to get out of your predicament. If the person sends an email to inquire about the circumstances the hacker answers as you and has money wired to the foreign country where armed with a false ID he can claim the money from however many people care for you. 

You can improve your odds against being hacked with a strong password but it doesn't have to be hard to remember. Let's say for example your name is Dave Jones. You can strengthen security by using upper and lower case letters so we will try DaVeJoNeS which is a step up from davejones which is rated weak by Microsoft 

With the DaVeJoNeS it rises to medium security which means that no one will accidentally guess your password even if they know you but it would not stop a hacker so lets go up one more step. We can add number and symbols from the top row of keys to help strengthen the password so we add a number to our medium password and Dave likes 7 so we get DaVe7JoNeS. Adding that one number makes our password in the strong category.

Now we will take Dave's password and we will add a few symbols in this case an asterisk in front and one in back or &*DaVe7JoNeS*& along with two ampersands and our password becomes very strong and hard to crack.

Here is another place where you can check your passwords but even with the Microsoft site I would send them a slightly altered password then what you intend to use because it is always safer to trust no one.

Feel free to share this with your friends because if I can stop one person from being hacked I consider it well worth the time it took me to write it down."

Thanks Buffalo!  

You can generate a random password, but it's difficult to remember things like >J^+B#8d.  

You could follow Buffalo's method but make it even sneakier by starting with someone else's name - the politician you hate most, perhaps?  A one-hit wonder pop-star from the 1980s? 

The Dreaded Error 64

Remember I said that we backed up databases over the network?  (Try to keep up). 

It meant that all the data went into one convenient place before going to a nightly tape backup.  And at first it all seemed fine, until…

<Cue Twilight Zone music>

Error 64…..

Every now and then a backup would fail.  The error message was – Error 64.  The SQL Server error logs come up with the helpful explanation “unknown error”. 

So for 30 servers, and each running a backup task for User and System databases, I might get half a dozen Error 64s a week.  There was never a pattern to it – at least, not one that I ever found.  Some days I would get several, other days none at all.  It didn’t seem to be linked to the SQL Server database servers – all experienced the problem occasionally, none had it consistently.  So maybe the problem lay at the other end of the network – at the destination media server.  Yet there were no errors there – everything seemed perfectly happy at that end. 

Look it up on Google, and you will probably find that “the specified network name is no longer available”.  From the start I suspected a network glitch, but our network specialists denied all knowledge of such a thing.  And certainly with the only errors appearing in SQL Server’s logs, it was hard to claim that it was a network problem.  Besides, while one backup fails because the network is unavailable, another backup over the same network, to the same place at the same time, worked perfectly happily. 

The majority of our servers are SQL 2005, but after a while the backup failed on a SQL Server 2000 server – but this time …

<Cue Twilight Zone music>

Error 22029…..

Apart from that, the symptoms were the same – “unknown error”, intermittent failure, no pattern discernable.  It’s difficult to discern a pattern when you only have a few 2000 instances, anyway. 

And just for the sake of completeness, when it happened on a SQL Server 2008 box…

<Cue Twilight Zone music>

Error 64…..

But instead of “unknown error” it says “The specified resource cannot be found” which sounds a little bit like “the specified network name is not available”, doesn’t it?

Workaround

First thing I tried was staggering the start times of the backups.  Originally everything set off at 1900, long after the pen-pushers and keyboard jockeys had gone home, and in plenty of time to get stuff copied to the media server for the backup to tape at 0200 the next morning.  So I set system backups going at 2 minute intervals, and then user backups starting at 2000, again at 2 minute intervals.  Did it make a difference?  Nah.  So it probably wasn’t overloading the system with some sort of bottleneck then. 

So how about a fallback position?  If the first backup fails, try taking another backup to a separate location.  Not a bad idea even without

<Cue Twilight Zone music>

Error 64…..

because of course the media server itself might fail.  So a second media server was made available, identical to the first one.  The maintenance plans were modified so that if the backup failed, it had another go and sent it to the second media server.  More detail here

This worked pretty well.  Although the error still happened, the secondary backup swung into action and took the backup.  Only very occasionally did both primary and secondary fail.  But it still happened.

Solved it!

I wish I could take the credit, but it was none of my doing.  One weekend the server team upgraded the two media servers from Windows Server 2003 to Windows server 2008.  They doubled the RAM from 4Gb to 8Gb while they were at it.  And the dreaded Error 64 was never seen again.  So if you have this problem, I hope this helps. 

                                       

Wrestling with Email

I get lots of them, so do you.  I get far more at home than I do at work, because I own or moderate about 20 Yahoogroups, and am a member of 20 more.  Plus all my LinkedIn stuff goes to my home email.  So how do I deal with them?

To start off, I use a thing called Tonsho to virtually eliminate spam. New senders are challenged which means that if there isn't a human sending it, it doesn't come near me. And the other good thing is that it's free (they make their money out of charging to send ginormous emails). 

Then I have rules set up to sort email into appropriate folders. If the subject is Project X, it automatically goes into the Project X folder - where I can get to it right after I finish Projects A to W <g>. Outlook automatically shows me how many unread messages I have in the Project X folder. At home my [Live-In-France] folder contains all the emails for that particular Yahoogroup - this lets me prioritise.  

Read and decide what to do with the Inbox - skim through it with your finger on the delete button.  If it needs a quick reply, then reply.  If it needs further thought, tag it and add a date for further action.  If it's something I am likely to want to keep, I move it to an appropriate folder (Useful Technical Stuff, maybe) 

I found this post Email Etiquette for the Super Busy by Jocelyn K Glei with some good advice:

Never send an angry or contentious email.  Put a five minute delay on your outgoing mail by setting up a rule in Outlook.  That gives you a chance for second thoughts - five minutes delay won't hurt - although it can seem like a long time when you are waiting for the messages to be sent!  

Be concise.  I don't use my Blackberry for email (I have work email at my office and home email at home, I don't need it in between as well).  But if I did I would be a bit peeved to get three page messages to read on a 5cm screen.  

Include deadlines - if there's a deadline, it's good to know that.  Burying the deadline at the bottom of the message is not as good as putting it at the top.  

Don’t send “Thanks!” emails.  Interesting advice. I usually try to say thank you, for example to people who accept my invites to link in. But Jocelyn argues cogently that I should NOT. That may be a step too far away from the human-human interface for me, but you can always say Thanks in Advance.