After all the confusion, I managed to get the database project template appear on my Visual Studio 2012 IDE. Then I installed the "Microsoft SQL Server Data Tools - Business Intelligence for Visual Studio 2012" and was happy to get the BI assets appear. Without much delay tried to create a SSIS project and everything went smooth. As part of further investigation, I created a script task and clicked on the Script task editor, then to my horror I saw it crashing, it opens another window and that crashes. The error description is Message=Field not found: 'Microsoft.VisualStudio.Progression.Actions.GetNodeAtSourcePosition'.
The error description did not take me any where. Finally, the following steps resolved the issue
1. Uninstalled and Install again Visual Studio 2012, select the SSDT feature ( this is optional )
2. Remove the SSDT from SQL Server installation.
3. Install "Microsoft SQL Server Data Tools - Business Intelligence for Visual Studio 2012" from http://www.microsoft.com/en-in/download/details.aspx?id=36843
4. This will populate the "C:\Program Files (x86)\Microsoft Visual Studio 11.0\VSTA" with required binaries.
5. Since I had re-installed Visual Studio, I had to install "SSDT for Visual Studio 2012" http://msdn.microsoft.com/en-US/jj650015. This will bring up the database project template.