Automated job generation

[kcbland]

Nope. Not using Server. You could consider writing a generator script that writes a .dsx file, after considerable time figuring out the structure of the file. Then, import the job and you're ready to go.

[Remi]

Ok, I see...

Thanks for your answer!

[chulett]

What Ken is talking about can be done but isn't simple. To get an idea of what would be involved, first create a 'skeleton' job that would be your base job with no metadata in it and export it to a .dsx. Then add in the metadata for one table and export it to another .dsx. Look at the differences between the two exports and see if you can come up with a methodology that could read a source of the metadata you'd need and generates a series of jobs based on that.

[kumar_s]

It will be greatful if you can share to all, your work if it is done.

[kduke]

I have created a job that can do this. I think Craig has one too. It took several days to get it working. Not sure I want to share. I sure don't want to answer questions about it. I think my template had 4 jobs to load a dimension table extract, hashed file xref job, load job and a sequence. It always assumes a straight table copy. You have to add any more logic than that. If 80 or 90 percent of your columns are straight copies then you have saved a lot of time. The assumption is your source and target tables are created. You have a simple table with the mappings. I extracted this mapping and read the original DSX and ripped out all references to the original source table and columns and replaced them with the new ones. The same of the target table and columns. A little tricky.

Several other assuptions where the link names matched the table names or could be created like CUSTOMER_DIM becomes CustomerDim. I have a function to do that. So you replace CUSTOMER_DIM and CustomerDim with ORDER_DIM and OrderDim.

I will sell you a copy of this process if you like. I will even modify it to generate jobs the way you want them. Send me a sample DSX export of your jobs and I can probably show you the rest.

I am working on adding this to DwNav. I almost have the table mappings done. I have a little work on the metadata piece as well to extract your source and target tables from whatever system tables you may have.

The mapping can be simple as well to assume every column in the source matches the target. All you have to do is replace with the real column name. Save you quite a bit of time I would think.

I want to design the targets in a similar manor. Assume all the target columns are the same name as the source. Map one main source table to this target. Then go about changing it and making it clean. Apply you naming conventions. Next you need to add other source tables. I doubt if you can automate too much of the extract and load of these. I would think fact tables are way beyond the scope of something like this as well. All the dimension tables would have to generate fairly cleanly before even attempting a fact table. Maybe version 11. DwNav is already version 5. I have a lot fun playing with it though. I love it. It doesn't cost much either. Way less than my consulting time to build this stuff manually. All my customers benefit from the time I save using this stuff. I don't think any will quit using EtlStats soon especially the QA stuff in it.

Maybe Craig will give away his version of generating these jobs.

[ray.wurlod]

ProfileStage can generate a target database specification and mapping document from source data (analyzed), and also generate DSX files. So clearly it's possible.

But, again, why do you want to re-invent the wheel?