How to Alter Table Using DTS just before data pumping?

• CDBA

  Hall of Fame

  Points: 3266

  More actions

  October 11, 2006 at 3:01 pm

  #76598

  I need to write a DTS which will import the data from an access file to SQL Server in a regular interval. But some time the access table will have some new columns added to it.

  While the DTS will be fired as a job, it needs to consider the new added columns also for adding in the SQL Server table. [That is current SQL Server table should be altered for a complete 1-1 mapping with the new access table].

  Can you please guide me how to achieve this goal?

  Thanks in advance.

  
  CDBA

• Michael Tocik

  SSCertifiable

  Points: 7099

  More actions

  October 12, 2006 at 12:20 am

  #665385

  Hi

  I think you are going to need to look at using the ActiveX taks.

  1. Query the Access table to check the structure

  2. Modify the SQL table if needed

  3. Import the data

   

  Mike

• CDBA

  Hall of Fame

  Points: 3266

  More actions

  October 12, 2006 at 8:36 am

  #665509

  Thanks.. I asked one of our developer to come up with the script which will compare both the tables and alter the SQL table before the data pump.

  But.. I am wondering.. how exactly the mapping for new colums will be taken care in DTS once the new cols are added?

  Plz advice. Thanks a lot  

  
  CDBA

• RH-296080

  Mr or Mrs. 500

  Points: 537

  More actions

  October 12, 2006 at 9:17 am

  #665520

  DTS will not be able to dynamically change its mappings once your source table changes.

  The other way i know is to use VB code to execute your DTS package. You can access all objects in the package and do your transformatiosn dyanmically.

  The easiest way to begin with this is to save your already existing package to a VBmodule.

  ---

  RH