Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
strauss_jon
Starting Member
23 Posts |
 Posted - 2011-10-10 : 10:17:32
|
| Hi, thanks for reading and taking part in this thread.I would be grateful if some SSIS guru's would post their views on their recommended techniques to build an SSIS ETL process (with emphasis on the balance between reusability vs simplicity). We have a system in place with many packages and tables. Per extraction, there is a dtsx package and 5+ tables (load, errors, staging, fk lookups, ods) and I'm looking for a way to shrink it all down and generally make it tidier, more maintainable (and gain some performance improvements in the process).In brief, we have approx 100 (nightly) extractions from Oracle and SQL Server which are co-ordinated via broker packages. The first process is the Extract and Transform. The Load (ODS) step is another process and I'm happy with this (we use a dynamically created merge statement with row checksums which works well and doesn't require flags to be set). With regards to the staging process though, I initially thought I could do this using one generic extract and transform package which uses a control table containing the information required to run the package; table names and their columns, lookup columns, SQL statements, order of executions, bespoke SQL and various other settings. However, this becomes way too complicated a framework to use and it renders SSIS useless for the job (SSIS either becomes a very thin client to SQLs and SPs or we end up having to dynamically create components which I would like to avoid). It's also non-intuitive to other developers who would need to add / maintain this framework so this is a no-go'er in my opinion. This is where my question of balance between reusability vs simplicity comes in. Presumably there's no getting away from having one dtsx per extraction (this isn't a big issue for me anyway becomes somewhere there has to be '1 something' for each extraction) but certainly getting stuff into staging after the transformations can be made generic, I just doubt it's worth doing the extractions and transformations dynamically with the goal of having everything 100% generic from the SSIS side.Anyway, I know there's loads of ways of doing this and I've got a few ideas myself. But I just wanted to see how other people do it, whether there's something I don't know about, if anyone uses control tables to dynamically extract from different sources 'on the fly' etc.Thanks in advance for your input.Jon |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-10-10 : 10:40:09
|
| I usually restrict an ssis package to a single table load. Then generate the pakcage from the table structure (and maybe a bespoke external query).The generator includes logging and error handling so if I want to change anything I just run the generator to recreate all the packages.These packages can then be called from an SP - or from an SSIS package if necessary.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
strauss_jon
Starting Member
23 Posts |
Posted - 2011-10-10 : 10:50:40
|
| Thanks NigelWhen you mean a package, are you referring to a dtsx package? I know how to generate an SP from a table, but a package? |
|
|
|
|
|
|
|
|
Reusable approach to SSIS ETL design