Did I say chaotic? Schizophrenic might be a better, if colloquial, description.
In truth, this is not unusual. Companies merge, form strategic alliances, break apart. People come and go, change jobs, learn to work differently. New technologies catch on, then fall out of fashion. And always, artifacts remain, encoded knowledge of the ways things used to work, resistant to change but evolving nonetheless, while cutthroat politics wax and wane.
Contractors are another kind of artifact. They are often highly experienced, and their encoded knowledge tends to be arcane and specialized. You would not be terribly off base to think of them as grizzled survivors of the IT wars, quirky veterans who have found their lucrative niches.
Arthur was such a contractor when my company brought him in to work with the maintenance group in 2005. He looked distinguished and patriarchal with his dark horn-rim glasses, salt-and-pepper hair, and black eyebrows. He had around 25-30 years of IT experience behind him. He fit the quirky image: he used Outlook to organize not only his time and email, but his notes about everything, the way people now use OneNote or EverNote.
And his niche? The ETL tool in Sql Server 2000.
Don't let my use of a three letter acronym throw you. ETL means Extract, Transform, and Load. Such tools are typically used in what used to be called Decision Support and is now usually called Business Intelligence. The Sql Server ETL tool was called Data Transformation Services, DTS for short.
- Sign on to Sql Server Enterprise Manager.
- Find the database you're working with, select it, and right click to open a context menu, then click on Data Transformation Services.
- In the window that opens in response to this request, you have a blank work area to the right, a column of icons to the left, and a menu bar at the top. The icons are divided into two sections: Connections and Tasks.
- Find the Database Connection icon and drag it to the work space; when you drop it, a properties window opens.
- Enter the database details, server, name, login id and password. Click Ok.
- Find the Excel -- this is, of course, the usual suspect -- Connection icon and drag it to the work space.
- Enter the path to the spreadsheet, including file name, into the properties window that opens when you drop the icon in the work space, then click Ok. Be aware that the blank spreadsheet must exist before you do this.
- Select the Database connection (data source), then the Excel connection (data target), and click Workflow in the menu bar. This opens a dropdown menu; click on Completion. This adds an arrow between the connection icons, pointing from the database to the spreadsheet to show the direction of data flow.
- Right click on the arrow and select Properties from the context menu.
- The resulting pop-up window presents a radio button list of options. Select Copy from database.
- The next window has five tabs; we are concerned with the first three -- Source, Destination, and Transformations.
- On the Source tab, select the database table to read and specify the desired data columns.
- On the Destination tab, select the spreadsheet columns to write to.
- On the Transformations tab, select each pair of corresponding source and destination columns, one at a time. There is a dropdown that gives a list of choices in how to process the data, ranging from a simple copy to a custom program; this time, select Copy.
- Once all the desired data has been specified, click Package on the menu bar, then Save As from the dropdown menu. Give the package a meaningful name and save it in the database.
- Run the package by once again clicking Package on the menu bar, then Execute.
- Debug if necessary.
- Repeat the previous two steps until there are no runtime errors.
- Confirm that the data in the spreadsheet is correct. If it is, we're done! If it isn't...
- This is a simple usage of DTS.