Hand coding ETL questions

[mugen_kanosei]

I know the numerous pros of using an ETL tool, but due to circumstances outside my control I have to hand code the ETL. My questions are in regards to actual coding practices. I am currently loading a couple of dimensions using perl. So far the entire load is in one perl script that is set to run every night. I'm wanting to code something more like what the books suggest, metadata driven, batch scheduling, etc. But im unsure how this is getting handled in general. At first I thought metadata driven meant that everytime the etl job runs, it goes to the metadata repository and pulls in the data to dynamically build the etl job. The script would house generic etl transformations and the repository would simple state the order to run these and the columns to use etc. But looking at some examples on the internet, it seems that some of the tools use the information in the repository to build the load script once, and then schedule that. Not unlike what I am doing now except Im hand coding all of it instead of using a gui to design it and let the tool build the script. Am I missing something, or is there a generally defined method of how to properly implement a hand coded system that I can reference? How have others hand coded their systems?

[ngalemmo]

I'm all for data driven design, but it would seem to me the effort to put something like that together would far exceed the effort to get the ETL done the old fashioned way.

Besides, have you looked at open-source ETL tools? There are quite a few out there that do a fairly decent job and they are either free or very low cost.

[mugen_kanosei]

I have looked at both Talend and Kettle. The problem is, that we have a strict list of software that is authorized on the network, and the review process to get new software on it would take longer than hand coding it. I will probably keep on as I have been and try to get a tool approved and purchased, and come back later and redo everything in the tool.

[Sponsored content]