www.idmwizard.com

I got a call yesterday from a colleague looking for help in importing a text file. For those that don’t know, I do quite a bit of work with a Quest product called ActiveRoles Server, and an add-on called Quick Connect. Quick Connect (QC from now on) is pretty slick, and has come a long way in the last 2 years. However, it can’t do everything quite yet.

The file that was to be imported was a fixed width file, meaning every line was the same width, as was every field. Which meant fields were padded with spaces. Unfortunately, QC cannot import those files out of the box today.

The initial thought was to write a pre-sync script that would alter the file and have it put the file into a usable format. However, that’s a lot of work, and a lot of scripting. And with someone on-site, may take a while with the added pressure.

So I made the following suggestion (note: SSIS is SQL Server Integration Services – the successor to SQL Server Data Transformation Services):
———————-
SSIS lets you take delimited or fixed width files and do whatever you want to them. I suggest you watch this:
http://www.idmwizard.com/quest/SSIS-CSVExport/index.html

Then create a package to do what you want (all wizard and gui driven – very easy). Once the package is created, and executing properly, take a look at the following command line command:
DTSRun

This will let you execute the package, which you can use in a pre-sync step. Also, SSIS can do any format to any format – you don’t have to have SQL Server (or any DB) as either end point. So you could actually use this to convert a fixed width (or ragged right) file to a CSV file that QC can consume.
——————————

Why do I think this is a better solution over a pre-sync step? Well, there are several of reasons, and some may disagree, but I’ll put them out there anyway:

1. Maintaining scripts is a pain – that pre-sync step will need someone “script-capable” to alter it when the file format changes (and it will – it always does)
2. GUIs are easier – the script is just that, while SSIS provides a nice GUI package editor – it’s simply a better tool and is less prone to allowing mistakes
3. Debugging – QC doesn’t have any debugger in it – so you try the job, and it fails, and you re-code. With SSIS, you get debugging, and you can isolate the transformation so you don’t have to run the whole QC job just to see if the file is being built correctly
4. SQL Server infrastructure – using a package around the transformation lets you add many, many other things to it – notifications, other data sources, and other options – you basically have all of SQL Server at your disposal. And the coolest feature is DTSRun which is a command line tool to run the package unattended. I’m all about the command line when possible.
5. Finally – Speed – I’ve found that DTS (now SSIS) is super fast. Its sole lot in life is moving data around. And while QC is quick, too, the SQL Server team has been working on SSIS for years, and is all about ‘speeds and feeds.’ In the past, I’ve had DTS packages that would process over 50 million rows of data – that is some serious data movement, and given that the file needs to get run twice (once to transform and once to load through QC), you might as well cut time down anywhere you can

So there you have it. Will Quick Connect have this functionality in the future? I can’t say for sure, but we’ve got a bunch of smart people working on the product. Why do we not have it already? We can put every conceivable feature into the product, but then we’d never ship it. What do you need to do to get it? Well, you need to let someone know. This is the first time I’ve encountered a fixed width file in 2 years. If its rare, then it will go to the bottom of the feature list. But if lots of people request it, it will rise to the top. And how do you let someone know? You contact someone at Quest (myself, the Product Manager, your sales rep, or even support) and ask for an enhancement request. Its that simple – and it all gets back to our PM who tracks this stuff.

Enjoy the video, and let me know if it helps.

Cheers,

Dmitry

Some folks have asked why there’s been no post up for over a month, and it had everything to do with WordPress.

After my last post, I upgraded to WordPress 2.9 – and that’s when all the trouble started.  Basically, I couldn’t log in.  I could get to the database, and I could get to the file system, but actually logging in gave me a message that I didn’t have rights to log in.  So my password was good, but everything else was a no-go.  I had been using Fantastico, which is great until it isn’t.  Fantastico is basically a management tool that automates deploying things like drupal, WordPress and other web apps out that my hosting provider uses.  It makes new app deployments much easier than even the built-in ones.  For example, with WordPress, it will create the DB, and set up your first user.

But I think its the fact that I deployed with Fantastico, and then upgraded inside WordPress that caused these problems.  And after lots of googling, searching, and experimenting, I gave up.  I actually needed to move the site to the main address (www.idmwizard.com instead of blog.idmwizard.com) for a while, and this gave me a swift kick in the backside.  Of course,

For those of you stuck with an upgrade, here’s what I did.  First, I deployed a very, very bare bones WordPress to the core site.  Which means I deleted everything in the root directory (except for .htaccess) and then put the default WordPress out there.  I then created the database, and edited wp-config.php.  Once that was done, I went through the default install (found at /wp-admin/install.php) and specified my new admin user.  Once WordPress did its thing, I came through, copied my original files (from the blog.idmwizard.com site) over the ones in the root for www.

Now, here comes the tricky steps – I dropped all but 2 tables in the wordpress database.  The tables I left were wp_users and wp_usermeta.  This would let me log in but then I could bring everything else over.  Next, I exported out my old database, and then edited the SQL file.  The edits I made were:

1. swap the order of the inserts for wp_users and wp_usermeta.  These two tables are linked, and trying to insert into wp_usermeta before the users themselves were updated would cause errors.
2. Remove any entries for user #1 in both tables – this was the main user, and the one I could not log in with, so I wanted to make sure it didn’t get screwed up again.

After that, I imported the database into its new place, and that was it.  It looks like we’re back to where we started (almost).  There are still things that are screwed up – I see my tags are missing from all my posts, as well as the categories.  I’ll need to sort that out later.  But the core site works, and I should probably re-tag everything anyway.

That is all . . . glad I finally got this done, and I’m going to bed . . .