14

Based on my analysis, a complete dimensional model for our data warehouse will require extraction from over 200 source tables. Some of these tables will be extracted as part of an incremental load and others will be a full load.

To note, we have about 225 source databases all the with the same schema.

From what I've seen, building a simple data flow in SSIS with a OLE DB source and OLE DB destination requires the columns and data types to be determined at design time. This means that I will eventually end up with over 200 data flows just for the extraction alone.

From a maintainability perspective, this strikes me as a big problem. If I needed to make some kind of sweeping change to the extraction code, I would have to modify 200 different data flows.

An an alternative option, I wrote a small script which reads the source databases, table names and columns I want to extract from a set of metadata tables. The code runs in multiple loops and uses dynamic SQL to extract from the source tables via a linked server and OPENQUERY.

Based on my tests, this is still not as fast as using an SSIS data flow with an OLEDB source and destination. So I am wondering what kind of alternatives I have. Thoughts so far include:

  1. Using EZAPI to programatically generate SSIS packages with simple data flow. The tables and columns to extract would come from the same metadata tables mentioned earlier.
  2. Purchase 3rd party software (dynamic data flow component)

What is the best way to approach this? When it comes to .NET programming I'm a beginner, so the time required to ramp up just with the basics is also a concern.

Improve this question
5
  • 1
    Since all 225 databases have the same schema, is it possible to maintain a view that unions the data from all 225 databases and point the SSIS package at that? While this may seem like a clobbering tool and won't necessarily perform magically, it sounds much easier to manage than 225 SSIS packages (even if you do manage some automation there). You could also go halfway and build a view for each set of databases, e.g. databases 1-25, 26-50, 51-75, etc.
    – Aaron Bertrand
    Commented Dec 14, 2012 at 21:42
  • The databases reside on multiple servers which I think makes it more complicated. I have actually tried creating a view of different tables on my development box against 225 databases and reading of the data was painfully slow.
    – 8kb
    Commented Dec 14, 2012 at 21:45
  • 1
    Well you would only want a view to reference databases on the same server. And again, a single view against all 225 tables is not going to perform magically, but I think you can still divide and conquer and not have 225 data flows.
    – Aaron Bertrand
    Commented Dec 14, 2012 at 21:49
  • Just curious how this all turned out for you op. We have a similar challenge on our hands.
    – RMuesi
    Commented Aug 2, 2023 at 19:16
  • We ended keeping the script that used dynamic SQL to load a single database. Every table in the destination database was given a new column to represent the source database it came from. Eventually, we wrote metadata queries to generate the script because of schema changes.
    – 8kb
    Commented Aug 4, 2023 at 0:07

2 Answers 2

Reset to default
14

I wouldn't want to have 200 data flows in a single package. The time it'd take just to open up and validate would make you old before your time.

EzAPI is fun but if you're new to .NET and SSIS, oh hell no, you don't want that. I think you'll spend far more time learning about the SSIS object model and possibly dealing with COM than actually getting work done.

Since I'm lazy, I'll plug BIML as a free option you didn't list. From an answer on SO https://stackoverflow.com/questions/13809491/generating-several-similar-ssis-packages-file-data-source-to-db/13809604#13809604

  • Biml is an interesting beast. Varigence will be happy to sell you a license to Mist but it's not needed. All you would need is BIDSHelper and then browse through BimlScript and look for a recipe that approximates your needs. Once you have that, click the context sensitive menu button in BIDSHelper and whoosh, it generates packages.

I think it might be an approach for you as well. You define your BIML that describes how your packages should behave and then generate them. In the scenario you describe where you make a change and have to fix N packages, nope, you fix your definition of the problem and regenerate packages.

Or if you've gained sufficient familiarity with the framework then use something like EzAPI to go and fix all the broken stuff. Heck, since you've tagged this as 2005, you could also give PacMan a try if you're in need of making mass modifications to existing packages.

SSIS Design considerations

Generally speaking, I try to make my packages focus on solving a single task (load sales data). If that requires 2 data flows, so be it. What I hate inheriting is a package from the import export wizard with many un-related data flows in a single package. Decompose them into something that solves a very specific problem. It makes future enhancements less risky as the surface area is reduced. An additional benefit is that I can be working on loading DimProducts while my minion is dealing with loading SnowflakeFromHell package.

Then use master package(s) to orchestrate the child work flows. I know you're on 2005 but SQL Server 2012's release of SSIS is the cat's pajamas. I love the project deployment model and the tight integration it allows between packages.

TSQL vs SSIS (my story)

As for the pure TSQL approach, in a previous job, they used a 73 step job for replicating all of their Informix data into SQL Server. It generally took about 9 hours but could stretch to 12 or so. After they bought a new SAN, it went down to about 7+ hours. Same logical process, rewritten in SSIS was a consistent sub 2 hours. Easily the biggest factor in driving down that time was the "free" parallelization we got using SSIS. The Agent job ran all of those tasks in serial. The master package basically divided the tables into processing units (5 parallel sets of serialized tasks of "run replicate table 1", table 2, etc) where I tried to divide the buckets into quasi equal sized units of work. This allowed the 60 or so lookup reference tables to get populated quickly and then the processing slowed down as it got into the "real" tables.

Other pluses for me using SSIS is that I get "free" configuration, logging and access to the .NET libraries for square data I need to bash into a round hole. I think it can be easier to maintain (pass off maintenance) an SSIS package than a pure TSQL approach by virtue of the graphical nature of the beast.

As always, your mileage may vary.

Improve this answer
2
  • BIML looks very interesting. I was also considering creating each data flow as a separate package and then having them called through a master package. Do you think that's any better? Also, curious if you have an opinion on the T-SQL approach. It's slower but I've tested it and it will work.
    – 8kb
    Commented Dec 14, 2012 at 21:58
  • I have updated my response with thoughts on design and pure tsql ETL approach
    – billinkc
    Commented Dec 14, 2012 at 22:36
0

You mentioned you have 200 source tables and 225 databases. I am assuming the 200 source tables is a count of all tables from all the 225 databases (cause if you had 200 tables in each database that will put your total table count to 45000). You also mentioned that the schema of the database is the same for the 225 databases.

You could build the SSIS packages for just the 1 database first and then when you schedule your jobs you can just change the database connection string using package configuration (if your SQL 2005, then you will be using package deployment model). As mentioned in the earlier responses SQL 2012 has new ways to configure your paremeters using the project deployment model.

You can get more information on package configuration with SSIS here http://www.sql-server-performance.com/2007/package-configuration-2005/

You can get more informatio on using project parameters from here, https://stackoverflow.com/questions/15206184/how-to-configure-ssis-2012-project-to-run-under-different-environment-configurat

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.