Post reply

How to Alter Table Using DTS just before data pumping?

  • 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

  • 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

  • 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

  • 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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply