Fixing Transformation file in Data Manager Package. Error - Transformation File does not exist.

[Former Member]

Hi,

We use BPC 10.0 MS with EPM Addin SP 16 Patch 1.

Our requirement is to hardcode (or fix) the Transformation File in the Import Package. For this purpose I have copied the standard Import Package and then I modified the script by adding the path to the Transformation File that I want the package to always call (Line 10). I also commented the Prompt to disable the screen which asks for the Transformation File (Line 3).

This is the modified script -

DEBUG(ON)

PROMPT(INFILES,,"Import file:",)

'PROMPT(TRANSFORMATION,%TRANSFORMATION%,"Transformation file:",,,Import.xls)

PROMPT(RADIOBUTTON,%CLEARDATA%,"Select the method for importing the data from the source file to the destination database",0,{"Merge data values (Imports all records, leaving all remaining records in the destination intact)","Replace && clear data values (Clears the data values for any existing records that mirror each entity/category/time combination defined in the source, then imports the source records)","Append data values"},{"0","1","2"})

PROMPT(RADIOBUTTON,%RUNLOGIC%,"Select whether to run default logic for stored values after importing",1,{"Yes","No"},{"1","0"})

PROMPT(RADIOBUTTON,%CHECKLCK%,"Select whether to check work status settings when importing data.",1,{"Yes, check for work status settings before importing","No, do not check work status settings"},{"1","0"})

INFO(%TEMPFILE%,%TEMPPATH%%RANDOMFILE%)

TASK(CONVERT data,INPUTFILE,%FILE%)

TASK(CONVERT data,OUTPUTFILE,%TEMPFILE%)

TASK(CONVERT data,CONVERSIONFILE,\FinanceEnv\SalesModel\DataManager\TransformationFiles\BPC Support\NationalData.xls)

TASK(CONVERT data,Environment,%Environment%)

TASK(CONVERT data,Model,%Model%)

TASK(CONVERT data,STRUSERNAME,%USER%)

TASK(LOAD AND PROCESS,Environment,%Environment%)

TASK(LOAD AND PROCESS,Model,%Model%)

TASK(LOAD AND PROCESS,USER,%USER%)

TASK(LOAD AND PROCESS,DATATRANSFERMODE,4)

TASK(LOAD AND PROCESS,CLEARDATA,%CLEARDATA%)

TASK(LOAD AND PROCESS,FILE,%TEMPFILE%)

TASK(LOAD AND PROCESS,RUNTHELOGIC,%RUNLOGIC%)

TASK(LOAD AND PROCESS,CHECKLCK,%CHECKLCK%)

------------------------------------------------------

However, on running this package I get the error -

Transformation file does not exist [\FinanceEnv\SalesModel\DataManager\TransformationFiles\BPC Support\NationalData.xls]

I have checked and the Transformation File exists. I have validated it a number of times.

What am I missing? Please advise.

Thank you

Raj

[Former Member · Accepted solution]

Hi Raj,

try adding an INFO line with your transformation file

INFO(%TRANSFORMATION%,%TRANSFORMATIONPATH%BPCSupport\NationalData.xls)

and change the red line as

TASK(CONVERT data,CONVERSIONFILE,%TRANSFORMATION%)

verify also that the .tdm files was created and it's present in the same path of the transformation file.

Please don't forget to close the thread after solved.

Regards

     Roberto

[Former Member · Accepted solution]

Thank you for the reply Roberto. Your suggestion worked!

However, I see that the package log now does not mention the Transformation File that was used. Although this does not affect the functioning of the package, I feel it is nice if the package log lists the transformation file used.

For this purpose, I un-commented the third line of the script responsible for prompting the user for selecting the Transformation File and changed it,

FROM -

PROMPT(TRANSFORMATION,%TRANSFORMATION%,"Transformation file:",,,Import.xls)

TO -

PROMPT(TRANSFORMATION,%TRANSFORMATION%,"Transformation file:",,,BPC Support\NationalData.xls)

The package now picks up the correct Transformation File (with the new INFO line that you suggested). But the package log shows the incorrect default path due to the change that I did above to Line 3 for PROMPT. It selects the defaut path and shows -

TRANSFORMATION = \FinanceEnv\SalesModel\DataManager\TransformationFiles\SYSTEM FILES\BPC Support\NationalData.xls

My BPC Support folder is directly under TransformationFiles and not SYSTEM FILES. No matter what path I provide, it shows the path till SYSTEM FILES and then the path I specify.

Question -

How can I prevent the log from showing the default path to see this -

TRANSFORMATION = \FinanceEnv\SalesModel\DataManager\TransformationFiles\BPC Support\NationalData.xls

Regards

Raj

[Former Member · Accepted solution]

Hi Raj,

try instead of Prompt instruction to add at the beginning of your package

DEBUG(ON) and see if it shows also the path of the transformation.

Regards

     Roberto

[Former Member · Accepted solution]

Hi Roberto,

My script already has the DEBUG(ON) as the first line (script shown in original question).

Is that is what you are referring to? Or did I misunderstand your suggestion.

Thanks

Raj

[Former Member · Accepted solution]

Hi Raj,

yes this was. Not all the packages shows the complete informations and Import is one of this other custom reports shows also INFO instructions in the log so I think it's better to leave Prompt instruction commented avoiding changes for mistake.

For the user is uninteresting which transformation is used and you can see it in the modify package.

Regards

     Roberto

[Former Member · Accepted solution]

I think I will agree with you Roberto. The user is not interested in which transformation file is being used, and I can always view the script to know which file is being used.

Thanks a lot for your help and guidance. I will now mark the original question as answered and close the thread!

Regards

Raj