0

I've inherited an environment that extracts data from a Dynamics AX 2012 ERP to a data warehouse for reporting. The original extraction set up by consultants did not include the proper date column needed by accounting. I have performed the following:

  • Created the column in the staging tables and destination tables
  • opened the packages in Visual Studio and found that each package file uses the same variable User::vSQLBase and contains the query that pulls all columns from the table needed in Dynamics AX. I altered that variable to include the date column.
  • I saved my changes and deployed the entire solution back to the data warehouse.
  • Ran the job in SQL Server, but none of the columns populated.

I did have an error appear in the debug of Visual Studio when I test executed the package.

Warning: 0x800470C8 at Incremental Load, Read Delta [26]: The external columns for Read Delta are out of synchronization with the data source columns. The column "Document Date" needs to be added to the external columns.

I did contact the consultant who replied with "the new column is not mapped to the staging table in the package, be aware that both the full load and incremental load need the same mapping. and a full load is mandatory since this is a new column". I am further unable to contact the consultant due to budget limitations. What am I missing to get the column to the staging table?

1

In addition to adding the new column to the variable User::vSQLBase, you need to open the Data Flow for the staging table being loaded. Edit the source, and click on Columns, verify you see the new column, click OK. Then edit the Destination, and click on Mappings - and make sure that the new column from the source is pointing to the right column in the staging table. That will do the new column mapping the consultant was talking about. You'll need to make sure the mapping is updated for whatever moves data from staging to the destination table too. Then save and deploy again.

An incremental load only populates new or changed data since the last successful run. Do you care about the new column being populated in the destination only going forward? Or is it important for that data to exist in records previously imported as well? For an accounting system, I'd guess you'd want the new column populated for all the records in the table. In that case, I agree with your consultant that a full load is necessary.

0

It turned out that after some one on one with a consultant we discovered that the staging tables in question were located in an additional database unknown to me. Once I updated the tables and stored procedures in that database I was able to deploy and populate the column.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.