Post reply

DTS auto mapping

  • July 13, 2006 at 6:45 am

    #169108

    Hello,

    Could you please provide me the activeX code which dynamically maps source and destination fields in the transformation.

    The sceniro am having is have 10 tables of diffrent fields and should be dynamically transformed from source server DB to destination database.

    Thanks in advance

  • July 13, 2006 at 6:51 am

    #648928

    Will the destination fields be a straight copy from the source fields with the same datatypes?

  • July 13, 2006 at 7:18 am

    #648942

    yes, evrything remains the same.

  • July 13, 2006 at 7:29 am

    #648945

    Then why don't you just build a sql command to do this for you?

    INSERT INTO DestDB.dbo.Table1

    SELECT * FROM SourceDB.dbo.Table1

    GO

    INSERT INTO DestDB.dbo.Table2

    SELECT * FROM SourceDB.dbo.Table2

    GO

    {And so forth}

    Then you can use one ExecuteSQL task instead of a transform.

  • July 13, 2006 at 7:53 am

    #648960

    yes, that can be done. what if i need to transform the tables to a text file. In this case i need a script that do auto mapping for each table by deleting the previous mapping.

  • July 13, 2006 at 8:21 am

    #648982

    Not necessarily. You can use the BCP utility.

    Look it up on BOL. If you don't have BOL, then here is a link:

    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

  • July 13, 2006 at 9:39 am

    #649010

    Thanks for all those clarifications. I have piece of code VB script which take care of auto mapping, the problem here is it's not working in loop.

    As u mentioned the insertion can be done in many other ways, but here I need to implement in activeX script only.

     

    For i = 1 To UBound(arr)

      Set oTransformation = tskDataPump.Transformations.New("DTS.DataPumpTransformCopy")

              With oTransformation

                  strField = arr(i)

       .NAME = "DTSTransformation__" & CStr(i)

                   .DestinationColumns.AddColumn strField, i

                   .SourceColumns.AddColumn strField, i

              End With

      tskDataPump.Transformations.Add oTransformation

     next

     Main = DTSTaskExecResult_Success

    End Function

     

    in the above code the array "arr" contains all the fields present in the table under transformation.

    Hope u can help me out solve this thru VB script.

     

    Thanks much

    Vinay

     

  • July 13, 2006 at 10:07 am

    #649030

    So let me understand this... Is this For loop not working? If so, what is the error message? If there is no error message, what is the behavior to make you think it's not working?

  • July 13, 2006 at 10:34 am

    #649039

    Here is how my package looks

    1) create a new source object

    2) create a new destination object.

    3) create a answer delete local task

    4) create a update source record task

    5) create an update 'R' record task

    6) create an update 'R' local task

    7) do the transformation

    above description is for one transformation (table), similarly I have to do it for 90 tables under a database.

    For this I have placed the all the table name in a table and retriviewing one by one thru looping and for each transformation.

    So, here the activeX script should take care to dynamic transformation, that is it should take source table (assigned Global variable) and Destination table name ( GV) and do auto mapping of fields and it should delete the previous mapping and transform data.

     

    The error am facing is the ETL picks the first table and do the transformation, when it picks the second table it fails at transformation as still the previous field mapping exist.

    Sorry for this lengthy explanation. hope this make sense if not am really sorry to bug u...thanks for ur patience

    can i have ur email id so that i can send the package for ur view.

  • July 13, 2006 at 11:34 am

    #649058

    Let's start with something simple. Insert this line right before the Next statement:

    Set oTransformation = Nothing

  • July 13, 2006 at 11:49 am

    #649064

    No luck!!..it didn't work

  • July 13, 2006 at 11:51 am

    #649065

    I sent you a message through this site with my email address in it.

Viewing 12 posts - 1 through 12 (of 12 total)

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