cancel
Showing results for 
Search instead for 
Did you mean: 

convert task failed

former_member200880
Participant
0 Kudos

dear team

we have a conversion task in SSIS pacakge .

this task has been failing everytime with the error below.

"The output file name is empty."

here is the Advanced dynamic script.

(ON)


(INFILES,%INPUT_FILE%,"Import file:",)


(MESSAGE,"Load Transport cost from flat file")


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


(%SELECTIONFILES%,%TEMPPATH%%RANDOMFILE%)


(BPCAPPSET,%APPSET%)


(BPCSQLSERVER,%SQLSERVER%)


(SELECTIONFILE ,CONNECTIONSTRING ,%SELECTIONFILES%)



(SELECTIONFILE,%SELECTIONFILES%)


(INPUTFILE,%INPUT_FILE%)



(CONVERT,INPUTFILE,%SELECTIONFILES%)


(CONVERT,OUTPUTFILE,%TEMPFILE%)


(CONVERT,CONVERSIONFILE,%TRANSFORMATIONPATH%\\Transport_Cost\Transport_Cost.xls)


(CONVERT,STRAPPSET,%APPSET%)


(CONVERT,STRAPP,%APP%)


(CONVERT,STRUSERNAME,%USER%)



(DUMPLOAD,APPSET,%APPSET%)


(DUMPLOAD,APP,%APP%)


(DUMPLOAD,USER,%USER%)


(DUMPLOAD,DATATRANSFERMODE,4)


(DUMPLOAD,CLEARDATA,0)


(DUMPLOAD,FILE,%TEMPFILE%)


(DUMPLOAD,RUNTHELOGIC,0)


(DUMPLOAD,CHECKLCK,0)


Accepted Solutions (1)

Accepted Solutions (1)

former_member186498
Active Contributor
0 Kudos

Hi Prasad,

I've seen you have a lot of thread "not answered". It is your duty to close the thread after you solved the issue assigning the "correct answer" and eventually the "helpful answers", this could help in the future other users to find quickly a solution for the same issue, so please close the solved threads.

Remember also the "assumed answered" is a way to close a thread if it's old but you don't find a solution, it should be used carefully.

About this issue, which package are you using? A standard import package? Can you please share the input, transformation and conversions files?

Regards

     Roberto

former_member200880
Participant
0 Kudos

hi Roberto

thanks for your reply.

its not standard import package. It is a custom build ssis package.

Attached the input file and transformation file..

sample input data

___________

MATERIAL_GMC,GEOGRAPHY,PRICETERM,UOM,TIME,RPTCURRENCY,SIGNEDDATA

62177152_40,WCSCUMF116,WCSCPTC14,FGCD1445,2014.Q1,OC_IDR,19574465350

64005214_40,WCSCUMF937,WCSCPTC14,FGCD1445,2014.Q1,OC_VND,3488100000

64005201_40,WCSCUMF937,WCSCPTC14,FGCD1445,2014.Q1,OC_VND,2745633000

transformation file

*OPTIONS
FORMAT = DELIMITED
HEADER = NO
DELIMITER = ,
SKIP =1
SKIPIF =
VALIDATERECORDS=YES
CREDITPOSITIVE=YES
MAXREJECTCOUNT=-1
ROUNDAMOUNT=

*MAPPING
RPTCURRENCY=*COL(6)
TIME=*COL(5)
SIGNEDDATA=*COL(7)
ACCOUNT_STD=*STR(TransCost_OC)
CYCLE_PL=*STR(CC_W_MEDIAN)
DATASRC=*STR(INPUT)
GEOGRAPHY=*COL(2)
MATERIAL_GMC=*COL(1)
MATERIALUSECOMMODITY=*COL(8)
PRICETERM=*COL(3)
UOM=*COL(4)

-------

the dimension materialusecommodity is not present in flat file. like wise account, ccyle_pl, .. we have hardcoded account and cycle_pl.

but the dimension materialusecomodity should be populated from the property of dimension material_gmc.

So what i have done is in SSIS package, I am transforming this flat file and then adding new column to another temp file. in that temp file i will populate the materialusecommodity memeber and put it in temp file.

this temp file needs to be sent to Convert task -> which undergoes Transformation file. and Hence Materialusecommodity mapped to col(8) in transformation file.

but the package fail log says. thebelow error.

The output file name is empty.

INFO(%TEMPFILE%, E:\PC_MS\Data\WebFolders\AAC_DEV\STANDARD_COSTS\PrivatePublications\Prasad\TempFiles\Temp8f2u_449_.tmp)

INFO(%SELECTIONFILES%, E:\PC_MS\Data\WebFolders\AAC_DEV\STANDARD_COSTS\PrivatePublications\Prasad\TempFiles\Temp500g_449_.tmp)

GLOBAL(BPCAPPSET, AAC_DEV)

GLOBAL(BPCSQLSERVER, CGTSAPP21391)

CONNECTION(SELECTIONFILE, CONNECTIONSTRING, E:\PC_MS\Data\WebFolders\AAC_DEV\STANDARD_COSTS\PrivatePublications\Prasad\TempFiles\Temp500g_449_.tmp)

GLOBAL(SELECTIONFILE, E:\PC_MS\Data\WebFolders\AAC_DEV\STANDARD_COSTS\PrivatePublications\Prasad\TempFiles\Temp500g_449_.tmp)

GLOBAL(INPUTFILE, E:\PC_MS\Data\WebFolders\AAC_DEV\STANDARD_COSTS\DataManager\DataFiles\\Transport_Cost_Unitary_file_Upload\TP_load.csv)

TASK(CONVERT, INPUTFILE, E:\PC_MS\Data\WebFolders\AAC_DEV\STANDARD_COSTS\PrivatePublications\Prasad\TempFiles\Temp500g_449_.tmp)

TASK(CONVERT, OUTPUTFILE, E:\PC_MS\Data\WebFolders\AAC_DEV\STANDARD_COSTS\PrivatePublications\Prasad\TempFiles\Temp8f2u_449_.tmp)

TASK(CONVERT, CONVERSIONFILE, E:\PC_MS\Data\WebFolders\AAC_DEV\STANDARD_COSTS\DataManager\TransformationFiles\\\Transport_Cost\Transport_Cost.xls)

I have checked the webfolder my private publications. I could see temp file Temp500g_449_.tmp got created but not the file temp8f2u_449_.tmp in my private publications.

however the header has come in the file temp500g_449_.tmp

here is header in above file

MATERIAL_GMC,GEOGRAPHY,TIME,PRICETERM,UOM,RPTCURRENCY,SIGNEDDATA,MATERIALUSECOMODITY

This means after the input file undergoing in the SSIS package, it is adding new column called materialusecommodity which is expected.

But the data didnt come to this file. Second thing is the outfile temp8f2u_449_.tmp not created in my private publications.

please let me know if this info helps

thanks in advance..

former_member186498
Active Contributor
0 Kudos

Hi Prasad,

first of all Thank You from the community for closing the answered threads in the right manner.

I'm not sure I've understand which your package is build.

You have 2 transformation file, the first without MATERIALUSECOMMODITY=*COL(8) and the second with it?

If not you will receive an error because the input file doesn't have COL(8).


however the header has come in the file temp500g_449_.tmp

here is header in above file

MATERIAL_GMC,GEOGRAPHY,TIME,PRICETERM,UOM,RPTCURRENCY,SIGNEDDATA,MATERIALUSECOMODITY

This means after the input file undergoing in the SSIS package, it is adding new column called materialusecommodity which is expected.

you have only the header MATERIALUSECOMODITY or also the field filled with values?

If you have built this custom package only to manage MATERIALUSECOMODITY, you can also think to use a standard import package using a conversion file that use EVPRO to extract the MATERIALUSECOMODITY from MATERIAL_GMC.

If you have a conversion file for MATERIAL_GMC you can add a hidden worksheet containing MATERIAL_GMC value as External the and MATERIALUSECOMODITY as Internal, extract with the evpro function and to made it more readable in the Formula you can put an EVDES of the Internal.

Regards

     Roberto

former_member200880
Participant
0 Kudos

hi Roberto

thanks for your reply.

To clarify, there is only one tranformation that is to Add account, data source and others.

We cannot use standard import package bcoz there are some other validations needs to be done in sSis package, besides custom log creation etc.,

and The inut file doesnt contain Material use commodity.. So wht iam doing is iam just passing the input file to SSIS package, there going some changes and creating another temp file adding another column called materialuse commidity which is in 8th column. and i will populate the members of this dimension by property of material_gmc dimension somehow.

now this new temp file needs to transformation file, so in convert task iam passing this new temp file which is having 8th column as well.

but when i ran the package the pacakge ends at convert task failure saying outfile name is empty.

please advise

thanks in advance.

former_member186498
Active Contributor
0 Kudos

Hi Prasad,

so probably the issue is on the file creation, try simply to save the new input file without import step and see if the file is created.

If created please share the new input file.

Regards

     Roberto

former_member200880
Participant
0 Kudos

hi Roberto

so glad for prompt responses from you.

I found the issue with ssis package where the header of input file are not matching with out file and inturn causing wrong mapping in transformation file.

i have fixed the issue. thank you so much for your help

regards

Prasad

former_member200880
Participant
0 Kudos

Hi Roberto

i am just thinking of another solution to this like...

Instead of passing the load file to SSIS package, I would like to create a conversion file which populates materialuse commodity. i.e I will map material use commodity also to materialgmc id in flat file.

and then in conversion file, i will pass materialgmc id in external and in internal i would like retreive the MUC property of it. [as you said earlier].

But when i am trying to do, the transformation file is not validating.. with evpro formula.

----

*MAPPING
RPTCURRENCY=*COL(6)
TIME=*COL(5)
SIGNEDDATA=*COL(7)
ACCOUNT_STD=*STR(TransCost_OC)
CYCLE_PL=*STR(CC_W_MEDIAN)
DATASRC=*STR(INPUT)
GEOGRAPHY=*COL(2)
MATERIAL_GMC=*COL(1)
MATERIALUSECOMMODITY=*COL(1)
PRICETERM=*COL(3)
UOM=*COL(4)

*CONVERSION
MATERIALUSECOMMODITY=test_trans.xls

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

conversion file

EXTERNAL INTERNAL

* js: EVPRO(,%external%,MUC_ID)

moreover, the evpro is not working in conversion file.

i tried like this also in ternal column like =evpro(,A2,MUC_ID).

even this not working.

can we use ev functions in conversion file? if so how.

please advise.

thanks in advance.

regards

Prasad

former_member186498
Active Contributor
0 Kudos

Hi Prasad,

yes you can use the EV functions in the conversion file but this isn't the right syntax, first remember that MS version doesn't support javascript only vb script.

If the filename is test_trans.xls you should have 2 worksheets the first called MATERIAL_GMC and the second MATERIALUSECOMMODITY, in this sheet you can use for External column

=IF(MATERIAL_GMC!A2<>"";MATERIAL_GMC!A2;"")

and for the Internal column

=IF(A2<>"";EVPRO("<YOUR_MODEL_NAME>";MATERIAL_GMC!B2;"MUC_ID");"")

and in *CONVERSION section you have to write

MATERIAL_GMC=[COMPANY]trans.xls.xls!MATERIAL_GMC

MATERIALUSECOMMODITY=[COMPANY]trans.xls.xls!MATERIALUSECOMMODITY

Regards

     Roberto

Answers (0)