Post reply

How to mapp columns at runtime in Data pump task(very important need ASAP)...

  • July 17, 2007 at 12:15 am

    #69029

    I have to mapp the source and destination columns at runtime.

    suppose I have source and target with connections aslo same. before this mapping or the datapump transformation I am creating a table in database with connection target, let's say I have created a table called orders by using the sql task, now the created table shd get the data from the orders table of source database with source as the connection, here the mapping of the source and target tables ahs to be done dynamically at runtime.

    If I enter a table name it shd create that table in target and the data pump transformation shd take place automatically.

    any help in this would be of great use, urgent....

    thanks in advance.....

  • July 19, 2007 at 5:20 am

    #720685

    Hi Sandy,

     

    I believe you could do this in an activeX script. And add the destination columns on your DataPumpTask.

    Take at look at the Disconnected Edit... properties of the package.

    (Right click anywhere on an empty pack of the DTS designer and you should get a context menu appear)

    If you expand tha Tasks and take a look at a DataPump Task it shows all the properties you can change in code. Expand the transformations and then one of the transformations you should get destination columns.

    I'm assuming you can add these at run time. Never tried adding items to a package at runtime only changing them. So you may have to set a dummy transformation first and then modifing that.

     

    Hope this helps,

     

    Rodney.

     

     

  • July 19, 2007 at 7:04 am

    #720736

    Sandy

    Best thing to do would be to create on datapump task with all the mappings. Save the package as a VB file (via Save as... command). Open the file and look at how all the field mappings are created. You'll need to do this for every table you want to transfer.

    Personally I think thats too much work for little benefit. Also, none of what you create will work in SQL 2005 Integration Services. Better to create seperate datapump task for each table.

    --------------------
    Colt 45 - the original point and click interface

  • July 25, 2007 at 12:51 pm

    #722398

    thanks for your replies folks,

    I have to mapp hte columns at runtime for the datapump task, I cannot do mannuall mapping to all becoz i have 30 to 400 tables.

    I want a generic packages which takes the table name as the input and shd mapp the columns from source to destination on fly.

    thanks.

  • July 25, 2007 at 5:16 pm

    #722445

    Then do as I suggested and save a package as VB so you can see how the mappings are created. Then add an ActiveScript task to do all the work.

    As I mentioned in my previous post, this is a lot of work and can be very troublesome (I've been in this situation myself and it wasn't fun at all). Then take into account that if you upgrade to SQL Server 2005 and Integration Services then you won't be able to do this dynamic mapping anyway. In Integration Services the column mapping is locked and can not be dynamically manipulated.

    --------------------
    Colt 45 - the original point and click interface

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

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