SSIS packages or Script for ETL

[tshad]

I am creating my ETL using SSIS packages move my 5 databases into one database.

The DBA at the client is real hesitant about using this instead of using a really long script file he used before that has all the T-Sql in it.

His reasoning is that the SSIS is too complicated and that no one at the company knows how to use it and he feels he would be forced to make any changes to it after we leave and would not be able to offload changes to other database personnel.

The reasoning may be valid from his point of view but I am trying to find good reasons why SSIS is better. Maybe I am wrong.

I am trying to find out if my reasoning is valid or not and if there are other reasons to use.

Reasons I gave are:

1. Able to break the ETL into more manageable pieces.
2. Certain tasks will run in parallel
3. Be able to handle multiple results of a query easier
4. Notification built in and easier
5. Error handling built in and easier.
6. Other DBs would normally like to learn and use the new technology which would also make them more valuable.

Thanks,

Tom

 

[blom0344]

DBA's 'get up and go to bed' with SQL scripts. From their point of view SSIS may seem like overkill. An ETL specialist will probably prefer the SSIS solution.

If this is a one time requirement then using a script is perfectly acceptable. If it is part of a regular process outside the DBA's competence then SSIS would probably be the better solution

Ties Blom

 

[RiverGuy]

Based on what you say, you are implementing the solution and handing it off to them where they will take over development. So I'd use scripts if that is what they are comfortable with. Also, if you have your ETL on the same server as the DW, you can get some quicker loads with scripts over data flow tasks in SSIS.

However, if you do plan on supporting the installation yourself, then you wouldn't want the client making their own modifications so you should be able to use what you want as long as they can provide systems support for it.