on 10-22-2014 2:15 PM
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)
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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..
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
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.
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
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
User | Count |
---|---|
6 | |
5 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.