Moving to October causes some trouble because we are moving from a 1 digit period (Jan = 1, Sep = 9) to a 2 digit period (Oct = 10). This causes problems during the Convert Task in SSIS. The actuals to transform text file contains all the correct data, the actuals to load text file contains only a header. The transformation fails.
To resolve this problem a change to the transformation file needs to occur. Follow these steps to make the change:
- eData > Manage Transformation File
- Select the file, in our case Actual CMS.xls
- Under the *MAPPING section find TIME=*COL(4) + *STR(.) + *COL(5) or TIME=*COL(4) + *STR(.0) + *COL(5)
- For 1 digit periods change *STR(.) to *STR(.0)
- For 2 digit periods change *STR(.0) TO *STR(.)
- Click on Validate and save transformation definitions in the Action Pane on the right.
- When prompted to Save, just select the same file, so in our case Actual CMS.xls.
- Click Yet to replace the existing file.
- Close the Transformation file