multiple ODBC stages on one layout

Apotluri · Post by **Apotluri** » Wed Dec 27, 2006 2:46 am

Hi All,

I need best practice suggestion for the following design

I designed the job in following way

(Staging Database) ODBC stage----->Transform------>(Target Database )ODBC stage

I was told that connecting to two databases from one job is not a good practice....

is this a good practice? what will be the problems if I continue with this design?

Thanks

ray.wurlod · Post by **ray.wurlod** » Wed Dec 27, 2006 3:39 am

ETL. Extraction, Transformation, Load. Any source, any target.

Find whoever told you "connecting to two databases from one job is not a good practice", demand to know why, and post the reply here so we can all have a good laugh.

DSguru2B · Post by **DSguru2B** » Wed Dec 27, 2006 7:15 am

I am already laughing

No offense apotluri, i was laughing at Ray's funny comments

Ultramundane · Post by **Ultramundane** » Wed Dec 27, 2006 10:25 am

Where I work it is not a good idea on some systems because of the very tight window for extracting data. By splitting the jobs, in my opinion, you reduce the chances of a failure during the extract. The extract job will likely run much faster and this helps to get the data out in the tight window. Especially when working with ODBC for loading data. In addition, if you have a failure during the load, you hopefully have the extracts still available. Thus, you don't need to go back and get the data again.

This is not a do-all, but a practice emloyed during modeling and job creation for those systems where we need such measures.

What will be the problems if I continue with this design?
I suppose that if the company you work for has established standards and you choose to not follow them that may you risk termination.

DSguru2B · Post by **DSguru2B** » Wed Dec 27, 2006 10:29 am

It depends upon the 'inhouse' standards. But as a general practice, its not outside the boundry of 'best practices' to access different databases in a single job.

ray.wurlod · Post by **ray.wurlod** » Wed Dec 27, 2006 3:46 pm

Ultramundane makes a good point. But "it's not good practice" is not the reason for splitting extraction from the other phases; there is a good reason (limited time window) in that case. I still believe that it's the right thing to do to challenge these vague assertions.

Be like a young, wide-eyed child, and don't be afraid to ask "why?". But don't accept "Because" as the answer.