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