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:
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:
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:
- 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)
- 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
- 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
- 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.
- 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.