I am trying to import an excel spreadsheet into a sql server database. The problem is I need the information in the spreadsheet to be imported into 3 different tables. I thought of transfering all details into a temporary table and then setting 3 triggers for each transfer of information. But this seems very inefficient. I know there should be an easier way to do this in dts.
Create a DTS package with Excel spreadSheet as the Source and the SQL Server as the Destination.
Create 3 Destination SQL Server and Define a connection between the one Excel Source and the three SQL Server destination table. It should just work perfect for you.
In the DTS tool bar there is a tool called Transofm task. Just select the source(excel) and Destination(sql server) and run it. You don't need to code any ActiveX script unless you plan to do some data messaging during the import/