Dynamic transformation mapping in DTS

Hi,

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
source).
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.http://www.sqlservercentral.com/columnists/bknight/convertdb2.asp

HTH|||Hello,

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.|||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.|||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.