What are the Best Practice Principles to develop SSIS Package

Question

Hi If I want to Develop SSIS Package from scratch what techniques/needs to be fallow in related to design,development,testing and deployment.

Answer 1

Here's a few tips:

1) Importing data: loading data into a table can often be sped up by first dropping nonclustered and the clustered indexes, then perform the data load, and finally recreate the indexes (clustered first, then the nonclustered).

2) This one applies almost everywhere: Avoid SELECT *.

Answer 2

IMO, SSIS is a software development discipline, just like C# or any other development tool/environment. There is a lot to it. I would start with a good book or tutorial. One of them can be found here: http://www.accelebrate.com/sql_training/ssis_tutorial.htm

Learn also the pros and cons of using the various connection types ADO, OLEDB etc. They each offer different functionality and transaction support. And, if you can, work with SSIS 2008 because it allows you to script objects using C#.

Randy

Answer 3

See my answer to a similar stack over flow question here

Answer 4

I think you should use some tools like BI xPress which can enforce SSIS best practices

http://pragmaticworks.com/products/Business-Intelligence/BIxPress/