How to approach the ETL mission?

Question

I have to do an ETL where the source is a large and poorly designed sql 2k database and a more advanced sql 2k5 database. I think SSIS is the way to go. Can someone suggest a to-do list or checklist or things to watch so I don't forget anything? How can I approach this so that he does not bite me from behind.

Answer 1

Well, I am developing ETL for the company where I am.

We work with SSIS. Using api to create and build our own dtsx packages.

SSIS . " OleDb", , .

API ( ).

, : http://technet.microsoft.com/de-de/library/ms135932(SQL.90).aspx

http://msdn.microsoft.com/en-us/library/ms345167.aspx

http://msdn.microsoft.com/en-us/library/ms403356.aspx

http://www.codeproject.com/KB/database/SSISProgramming.aspx?display=PrintAll&fid=382208&df=90&mpp=25&noise=3&sort=Position&view=Quick&fr=26&select=2551674

http://www.codeproject.com/KB/database/foreachadossis.aspx

http://wiki.sqlis.com/default.aspx/SQLISWiki/ComponentErrorCodes.html

http://www.new.facebook.com/inbox/readmessage.php?t=1041904880323#/home.php?ref=logo

http://technet.microsoft.com/en-us/library/ms187670.aspx

http://msdn.microsoft.com/ja-jp/library/microsoft.sqlserver.dts.runtime.foreachloop.foreachenumerator.aspx

http://www.sqlis.com/post/Handling-different-row-types-in-the-same-file.aspx

http://technet.microsoft.com/en-us/library/ms135967(SQL.90).aspx

http://msdn.microsoft.com/en-us/library/ms137709(SQL.90).aspx

http://msdn.microsoft.com/en-us/library/ms345164(SQL.90).aspx

http://msdn.microsoft.com/en-us/library/ms141232.aspx

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx

http://www.ivolva.com/ssis_code_generator.html

http://www.ivolva.com/ssis_wizards.html

http://www.codeplex.com/MSFTISProdSamples

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/Q_23972361.html

http://forums.microsoft.com/MSDN/MigratedForum.aspx?siteid=1&PostID=1404157

http://msdn.microsoft.com/en-us/library/aa719592(VS.71).aspx

http://forums.microsoft.com/MSDN/MigratedForum.aspx?siteid=1&ForumID=80

http://blogs.conchango.com/jamiethomson/archive/2005/06/11/SSIS_3A00_-Custom-Logging-Using-Event-Handlers.aspx

http://blogs.conchango.com/jamiethomson/archive/2007/03/13/SSIS_3A00_-Property-Paths-syntax.aspx

http://search.live.com/results.aspx?q=%s&go=Buscar&form=QBJK&q1=macro%3Ajamiet.ssis

http://toddmcdermid.blogspot.com/2008/09/using-performupgrade.html?showComment=1224715020000

http://msdn.microsoft.com/en-us/library/ms136082.aspx

http://support.microsoft.com/kb/839279/en-us

"", .

Answer 2

ETL

• (, , ). - ETL. .

• SQL2000 , , , , SSIS . , ETL ; - . sprocs, , sprocs.
  
  , , 100% - sproc , - . ETL/sproc , , .

• ETL - , .

• , . , ETL, .

• - . , , . , . sproc, .

• . unit test . , , . , . , , 600 700 T-SQL. .

• , .

• - , , , ​​ . . , - ( b, A: B 1:1).

• . . - . , " ", "" " " .

• ( ), ODS . , ODS , . ODS - , - . - , ODS ODS .

1-2 4-5 , , (, ) . .

3 2. SQL ETL . , . , . , , .

6 . SCD , , , . , . , .

7 , . , ; .

8 . , - , . , , .

9 . , . .

10 - , ODS , . , ODS (: ) ODS.

Answer 3

ETL, 200 + , , . , , . , , , , :

• , , , . , ( , , ).

• . , , , , .

• , , . , , , . , . , , , , , , . .

• . , , , , .

• , , . , , . , - - , . .

HTH, , - .

Answer 4

ETL ( AS400 Oracle EBS), , ( ) :

• /.
• /.
• - .
• .
• , .
• .
• ETL 1 - 3.

- 2 3 - - , ( - , !). .

Answer 5

, ConcernedOfTunbridgeWells. . , , .