Lesson learnt for the day is when you try to connect to SQL Server Desktop Engine (MSDE) remotely, the scenario where i tried to connect was from SSIS - Sql Server Integration Services. I tried to create a OLEDB connection. When i tried to create the OLEDB connect the same like we use to do for SQL server; the following error message was shown
Solution: When i googled, came to know that we need to give the Server name with instance name when we create a connection.
And we need to give the database name if not displayed in drop down list.
Hope It helps