Generic staging design of data warehouse

I have a question about staging design using SSIS. Has anyone come up with an ETL design that would read table names from a generic table and dynamically create the ETL to stage the table.

1. Have a generic table which would have table name and description and whatever else that was required.

2. Have a master ETL that would enumerate through the table and stage all the table names found in the generic table.

This way I wouldn't have to create an ETL which would hardcode the names of 300-500 tables and have the appropriate 300-500 data sources and targets listed.

Not sure if I am making sense but I hope someone understands the attempt.

thanks

Assuming you know the metadata of each table this should be possible. It won't be easy - but possible.

You'll need to generate a package using code. There are examples in BOL and this post should help out - http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/12/31/17731.aspx

-Jamie

|||

Thanks for the the lead.

In my case, I'm trying to read from Oracle and move the table(s) to SQL Server. I assume this would not work similarly as the task that is mentioned is only from SQL Serer to SQL Server.

|||

In Oracle, you can hit some of the system tables to generate them like all_tables and all_tab_columns to retrieve the metadata you would need to do this. Like Jamie said, it will be a lot of work to do and you'll learn a lot about the object model by doing it :).

-- Brian Knight

|||

I apologize for not being more explicit with my requirements. I already know all the Oracle tables I need which with Oracle Applications can run anywhere up to 10000 tables depending on which applications are installed.

In our staging strategy where we move our Oracle tables to SQL Server we'd like to get away from creating N number of packages or one large package with N number of tables we require which could be at least 500. The idea of having to hardcode 500 OLE DB source objects and 500 OLE DB target objects seems a little archaic at best to me. I'm just wondering if a better strategy would be to be able to access a SQL Server table where we would maintain a list of those tables required (which would include more metadata information for our own operations staff which we can't using the Oracle FND_TABLES or ALL_TABLES/ALL_OBJECTS).

We would then have one ETL job that would essentially read from this table and dynamically create the 500 OLE DB source and target objects required to complete our daily staging routines.

The prior example which Jamie eluded to seems to only work with SQL Server source and target situations. Is there some way we could accomplish this using Oracle source to SQL server target and how would I best get started on this?

thanks

|||

Yes, the example only uses SQL Server sources and targets but the theory and the approach are roughly the same. It is still just an OLE DB source so really, there isn't any difference. just a different connection string.

-Jamie

|||You could use the Import Data context menu to not only build your Staging Schema but also to build your extract package.

On the first Import Data process, link to your Oracle box and select

the tables you want to import. You can optionally set destination

column types. If you have clob/blob columns, you will have to

process them manually. Once your done setting your options, just

run the package.

On the second Import Data process select the same tables. Here is

the annoying part, for each table you need to set the option to

append. When your finished, save the package. You'll then

have an extract package for all 500 tables.

Larry Pope|||

If you know T-SQL and PLSQL well enough I'd not bother with SSIS...

Just create set of scripts based on meta-data that:

- create target table

- dumps text file with data

- load tables in SQL Server field by field (may be by using Bulk Insert)

- map data types when needed for blobs and so on

The scripts would accept a table name as parameter. Then loop through system table in Oracle Db and run that script.

I don't think SSIS transformation toolset is mature enough to do things like that...but you can use SSIS or out it all together in a workflow.

|||

I guess I don't completely understand, sorry!!! Where is the Import Data context menu?

It still sounds like much manual work. I was hoping for something more dynamic.

Sorry, for the questions but I'm new to SSIS. I have been using Business Objects Data Integrator product which is quite different in many aspects.

thanks

|||The Import Data context menu can be accessed in SQL Management

Studio. Right-Click on the destination database and select

Tasks. The option is near the bottom.

If you don't have the schema setup in your staging database, it is alot

easier to use the wizard to generate your tables. If you don't

make any changes, it should only take a few minutes to build the

schema. But the package will also copy data over as well, so if

you have data in the source it might take awhile.

As far as the amount of manual work, that's a matter of opinion.

Would you rather develop a package that can read tables/schema from

Oracle, checks tables/schemas on MSSQL and dynamically populated them

OR run through a wizard and manually set the append option on each

individual table.

There are advantages and disadvantages to both. One big advantage

to the dynamic approach is that the destination table(s) will

automatically be updated with schema changes. The big advantage

of the Import Data method, is that it will take significantly less time

implement.

Larry Pope|||

Thank you very much for the additional information and guidance. I'll take a look at your recommendations.

John