Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2005

    Unanswered: Dynamic transformation mapping in DTS


    I need to export data from SQL tables to AS400 files(the SQL table has the
    same file name and column names as the file on the AS400) .
    I created a DTS that has the following tasks: dynamic properties task, SQL
    server connection, transform data task and a other connection(ODBC data
    I'm using global variables to dynamically set the source and destination
    tables names on the transform data task. The problem is the transformations
    are not automatically mapped and I get an error message when the
    DTS package is executed with a source and destination that has
    different columns than the ones specified in the transformation.

    Any ideas or possible workaround would be greatly appreciated.
    Thank you very much.

  2. #2
    Join Date
    Aug 2002

  3. #3
    Join Date
    Dec 2005

    Dynamic transformation mapping in DTS


    Thank you for your reply, however I read the article for the link you supplied
    and it doesn't solve my problem. My fault. I will elaborate and try to be clearer.

    As you know, I need to export data from SQL tables to AS400 files(the SQL table has the same structure and column names as the file on the AS400).
    I created a DTS that has 2 global variables for the source file and the destination table. I'm supplying a different source and destination file name every time I execute the DTS package(executed from a stored procedure).
    In other words, on the first execution of the DTS, the global variables SQL server table A(source) and AS400 file B(destination) will be passed. On the second run, the variables will contain table name C and file name D. So on and so forth.
    I need to re-create mapping every time the package is executed because I don't know the column names ahead of time.

    In a nutshell, the data transformation task allows you to dynamically specify
    a source and destination table name, but consequently, doesn't automatically generate the column mappings.

    I don't think the problem lies in the fact that it's an SQL server to AS400 data transfer, but rather, how to create auto mappings based on a given source and destination.

    Sorry if I'm being repetitive and I hope all this clarifies my issue.

    Still hoping for a solution or workaround!
    Thanks once again.

  4. #4
    Join Date
    Jan 2005
    Green Bay
    In my experience Dynamic Mapping is not possible.

    Can you link the databases and dynamically write the insert statement?

    Can you create a vbsript to create a text file that always has the same layout but different sql source?

    Then do your load on AS400 the same way?

    Allow each system to control process on itself.

    Just some possible suggestions.

  5. #5
    Join Date
    Dec 2005

    Dynamic transformation mapping in DTS

    I'm afraid you're right about dynamic mapping not being possible.

    Also, you seem to have some good suggestions to a workaround, however
    you would need to elaborate because I don't know how to implement them.

    Can you provide one or more examples?

    Appreciate the response.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts