Interveiw question : how you approach SSIS packages desing and development ?

Question

i came across one job interview question:

how yo approach SSIS packages design and development ?

pls anybody help me out...

might be this same question i will get in next interveiws ....

any help is apprciate..

Answer 1

now thats tricky ...
few steps
1. understand requirement ...
2. list down business rules / transformations required for the etl
3. capture frequency of package runs .
4. loggging of warnings errors for package run
5. capture information like what to do with records which fail(in ETL ie record tuple which do not pass through ur transformation basically bad records....)
6. write down test cases and cater the test failure actions in package ...
7. performance analysis ...
dot dot dot ....:)
hope that helps
___________________________________________________________
Please mark post/s helpful / answered if tehy anser ur question -- kunal

Answer 2

i came across one job interview question:

how yo approach SSIS packages design and development ?

Hmmm...that is tricky...you could say you hire an SSIS expert for a week....

Here is a link which maybe very helpful:

Integration Services Programming

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

Answer 3

1.Understand the requirement.
2.Plan the design of the package.
3.Consider the performance of the package while designing. (Best practices for package design)
4.Try to figure out the pattern in all the packages Ex:If all the packages require sending a mail on failure (just an example), try creating a  
   template package (to avaoid declaring the same to,from,cc,...variables again and again)

You should be aware of the functionality of all the components. Also, you should be aware of disadvantages of uing certain componnets (ex: OLEDB Command Component). It might be possible that OLEDB Command will fit in your package at some point, but try to avoid that and use other option like SET based operations at control flow.

Certain other things to be decided by you:
How to use configurations (if neded)?
Naming standards.

And the list goes on....

Check the link given by Kalman

---

Nitesh Rai- Please mark the post as answered if it answers your question