natkey-biml.txtI have created a package that transfers data from an old database to a new one. This looks something like this;
tblSupplier -> Suppliers
tblNaturalKey -> InvoiceNaturalKey
tblInvoice -> Invoices ...
Table names are not an issue as they can be hard coded in. I want to automate this for many clients and each has their own natural key for invoice table.
For example [InvoiceNumber], [AccountNumber]....
So for each client i have created a package that has an execute SQL task that retrieves column information from tblNaturalKey, stores as object and then implements an ADO loop to read object and create each custom column in the new Natural Key table. I hoped then the transfer package could just pick up the mappings based on column name equality. This is dynamic as i can just enter old and new server and database as environment variables and run the job from a server catalog. But the columns won't map and i get metadata errors. I will attach my source biml code. Please help! KR Marcus