Browse DevX
Sign up for e-mail newsletters from DevX


Coordinate Your DTS Tasks, Part I: "After the Fact" Data Updates-4 : Page 4




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Hindsight Helpers
As so happens in the course of development, ideas that would've improved or refined the initial method used to create a solution crop up after the fact. So its no surprise that after I got this month's 10-Minute Solution working, I got a few other ideas that would have made it more elegant.

It would have been more elegant to replace the batch file with the Scripting Runtime File System Object (FSO). FSO has methods that know about the folder name, the folder's files collection, and each file's attributes (its name, date, and time). It can also read file names and rename them too.

The main advantage of using FSO would have been eliminating the text file altogether. By putting the FSO inside a for loop that would execute once for each file in its file collection, the operation that loads the text file and moves the cleaned file name to the location table could be reduced from a two-step process to a one-step process. Unfortunately, this idea came to me after the process was already in production. So I didn't follow up on it, but it does strike me as a more elegant way to accomplish this same end. The file names and file types would remain available, and you could either use the file's date and time, or else retrieve the update time using getdate(). You could even make the process more lightweight by saving the file's date and time and the time it was last loaded (the getdate() value), and use these as kind of a timestamp. If the file date and time did not change since the last time the location table was populated, you would know that the Excel worksheet was not changed. Therefore, it would not need to re-imported. Ah, but hindsight is always at least 20/20.

In next month's 10-Minute Solution, I will show you how to use the contents of this table of file names to feed two more downstream processes that open up each Excel file and insert their contents into a staging table. Then I'll show you how to create secure processes that encapsulate these sub-processes into higher-level packages. Stay tuned.

Drew Georgopulos is the database architect for GoAmerica Communications. He moonlights as an adjunct professor of computer science at Marymount College of Fordham University, teaching systems analysis and relational database design. .
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date