Phew,today I decided to get into my development environment in Windows 7 and fix it up for good.
SQL Server 2005 Enterprise Edition, which I had installed previously had issues with the SQL instances. I ran 'services.msc' to find that SQLExpress was not running. I tried reinstalling SQL Server a couple of times from the CD, before realizing that it did not include SQLExpress server.
I then had to download it from the Microsoft Website and install it. Before installing it, I removed my existing installation of Visual Studio 2008 and SQL Server 2005 as well. It must be noted that for Windows7, it is mandatory to install SP3 of all SQL Server components. So, I did that and finally(by afternoon), got it running.
Next issue was with SSIS itself. Through some googling, I got to know about derived transformations and lookups. I just managed to finish up a partial setup of my required workflow, a few minutes ago. Rest of it, I have to continue from office, after some discussion with my senior. Some alternative solutions exist for it. Btw I will briefly explain the required workflow here. It might be pretty easy for those used to SSIS, but as a fresher it was pretty tough for me.
An excel file contains about 5 workbooks, each to be migrated to corresponding SQL tables, with additional fields of IDs,Creation/Modification Timestamps and By info. Also, to be implemented is a history mechanism, which just means that whenever a field in excel file is modified, the package must detect the change, and update the table with changed data, and insert that info into the history table. This needs that every update to transaction table be clubbed with an insert to history table. I am clear with the requirement,but neither the workflow design nor which transformations can help me achieve these.
Lets see...btw here is a screenshot of the workflow designed so far