Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm currently in the process of creating ETLs for our data warehouse. We're using SSIS 2008, but we're running into issues, the biggest of which is the difficulty in re-using components. We have separate packages for each table and each package takes as input a number of variables from a parent package. As we make changes to these input variables, we're required to go into each package (we have 15 or so now, but this number is going to grow significantly) and modify the package to deal with those changes. There are also other issues, including the inability to run arbitrary SQL for our extraction, poor logging capabilities, etc.

This entire process would be much more robust if there were a way develop our ETLs in code, enabling code reuse, common libraries, better unit tests, etc. Is there a de facto standard ETL language / API for SQL Server? I'm looking to avoid GUI tools as much as possible.

Edit: I should mention my background. I'm not a DBA and have no formal (or informal) DBA training, I've basically figured this stuff out as I went along, so there is every likelihood that I'm attempting to do inappropriate things with SSIS or approaching this ETL project from the wrong angle. Also, I'm currently employed in state government, so any solutions that require the purchase of a new software package aren't within the realm of possibility.


Here's one of our tasks. We are using a single SSIS Package to load each table in our warehouse. Each Fact package and Dimension package are generally the same, they only differ in

  • Extractions from the source database
  • Manipulations in a Data Flow
  • Merges into the destination table

What I would like to be able to do (that I'm finding to be difficult to do in SSIS)

  • Load the extraction query from a text file. When developers are writing and testing their extraction queries, I should not have to manipulate their query in any way before SSIS runs it and I should not have to cut and paste the query into a DB Source object.
  • Test each component individually. I should be able to test the complete ETL process for an individual table in isolation, independent of other table loads.
  • Make modifications to the shared logic in one place, not have to edit each individual package. Every package loads data into the audit tables the same way, if I want to change the data that is loaded audited, I don't want to have to edit all 15 packages (this number is going to get much much larger over time).

The entire process feels like it would be much easier to implement and more robust if done programatically with proper use of shared code.

share|improve this question
4  
I am NOT a very big user of SSIS but can understand the perception of steep learning curve here. I encourage you to look at some videos/blogs of Andy Leonard, Jamie Thompson, Brian Knight who are experts on the field and get some direction. Look at sqlpass.org website for free videos of pass summit & sqlblog.com, pragmaticworks.com –  Sankar Reddy May 25 '11 at 15:39
    
I don't believe the learning curve is a problem. I know how to do the tasks I want to do in SSIS. I'm looking into a new process because the solutions I've found are repetitive, fragile and needlessly complex. –  kubi May 25 '11 at 15:51
    
Kubi, If you can add details on which components you are referring to, I will bring someone capable to answer that for you. As it is right now, your question is too broad to answer. –  Sankar Reddy May 25 '11 at 16:50
4  
@kubi - you've touched on one of the dirty little secrets of the B.I. industry. ETL tools are very, very poor at abstraction and reusable logic. As a consequence they scale very poorly with increasing domain complexity. –  ConcernedOfTunbridgeWells Apr 19 '12 at 16:27
1  
I have it on fairly good authority that about half of the customers of a certain industry vertical product for banking and insurance (made by a company you've heard of and usually referred to by a specific colour) make an explicit technical decision to build their ETL processing in stored procedure cude, for precisely this reason. –  ConcernedOfTunbridgeWells Apr 19 '12 at 16:28

4 Answers 4

There is a tool that enables this - http://www.varigence.com/products/biml.html

There's a commerical version, but we also include some of the BIML functionality in BIDS Helper, a free tool. http://bidshelper.codeplex.com/

I'm happy to answer any questions that you might have about it.

This is a tool that my company provides.

share|improve this answer

Upon reading this I immediately thought of recommending Varigence's tools. However, I see that one of the chief architects at Varigence, John Welch, got here before me.

Varigence's tools are an abstraction layer above SSIS. The advantage that that provides is an ability to define reusable "stuff" thus providing consistency across multiple packages. You define how packages should be structured and how they differ on an individual basis -- the "compiled" outputs from Varigence's tools are SSIS packages.

Think of it as Dynamic SQL for SSIS packages. With a GUI. Really really cool.

share|improve this answer

I tried using SSIS several times, and gave up on it. IMO it is much easier to just do all I need in C#. SSIS is too complex, it has too many gotchas, and it is just not worth it. It is much better to spend more time on improving C# skills than to spend same time on learning SSIS - you'll get much more return on your training. I do not need to go into much detail here - Ayende wrote a great summary I have nothing to add to.

Also finding and maintaining functionality in a VS solution is so very much easier. Unit testing with VS is easy. All I need to do is to check in the source in Subversion, and verify how it loaded. Unit testing SSIS packages is very involved to put it mildly.

Besides, there were situations when SSIS was silently failing to populate some columns in some rows, just skipping them without raising exceptions. We spent a lot of time troubleshooting and figuring out what is going on. Developing an alternative solution in C# took less than an hour, and works without any problems for two years.

Also Rhino ETL seems to be really cool.

There were a few similar discussions on stackoverflow.

share|improve this answer

Personally, I handle as much of the ETL process as possible in SQL. I use SSIS to import from odd data sources like FTP sites or Excel, but that's just to get raw data into the database where SQL does the rest.

My current situation is relatively simple in that most of the data is in other MS SQL databases, with whom I can set up linked servers. If you have to connect to other platforms, I recommend using OPENQUERY and BULK INSERT. They can be constructed programmatically if necessary, and between the two of them they can connect to most types of data.

I use SQL because it's what I know best, but it has some objective advantages. Most notably, it's already being used: there's no need to learn or pay for a new tool. It's a widely-available skill, which should matter to your boss if not to you. Since it operates in the database, logging is easy. It's based on plain text code, so it's easily searched and works well with source control. It's very stable, with very little chance of the vendor changing things and breaking backward compatibility. It's probably at least as fast as any RBAR language.

If you need more, I recommend .NET, if only because it's used in SSIS and SQLCLR. I use C# apps to manage the overall ETL process - starting sub-steps, monitoring their output, sending e-mails. But almost all of this could be done with SQL Agent, dbmail, etc.

Is there any reason you can't use SQL for your ETL? What has it not been able to do for you?

share|improve this answer
    
Indeed we use SSIS to dump raw data into Temp DBs then we use TSQL define how we want to T and L it. –  Paul Apr 17 at 9:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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