ETL and Data Integration tools - Modern ETL software for everyone

Hi Hello

I am currently trying to get the sequence number script working –

var FileHandle: Integer;
FileName: string;
I: Integer;
Begin
FileName:='c:\1.txt';
if FileExists(FileName) then
begin
FileHandle:=FileOpen(FileName,fmOpenReadWrite);
FileRead(FileHandle,I,4);
I:=I+1;
FileSeek(FileHandle,0,0);
FileWrite(FileHandle,I,4);
FileClose(FileHandle);
end
else
begin
I:=1;
FileHandle:=FileCreate(FileName,fmOpenWrite);
FileWrite(FileHandle,I,4);
FileClose(FileHandle);
end;
SetVariable('<file_id>',LeftPad(IntToStr(i),4,'0'));
Result:=i;
end;

In running it as the first part of the package it is failing with the comment of “Failure” and no guide as to where or why.

It is creating the 1.txt file but I cannot see what is causing the failure – I have attached the log file and the txt file created by the script.

Could you please see if you can identify what is causing the error in the script

Regards,
Andrew

Have you got the file open?
It works fine for me.

Mike

Hi Mike

When you say it worked for you are you actually running in a package in the ETL Enterprise environment or some other environment?

I cannot get anything other than the same error - -

Running the first time creates the file and gives an error , running it a second and subsequent times always gives the same error.

In the attached screen shots I tried cutting down the script to just what should be required to increment the sequence number once the file was established

Line 6 of the attached script is the Open statement to get at the file and after you run the package the file modified time stamp has been changed.

Are there any other environment set-ups that are needed for the script to work?

I was working with 4.2.6.1 but have since upgraded t 4.6.2.7 in case there was something required in the later releases

The current implementation schedule was for testing to start as from tomorrow’s run for MY which I was going to hopefully set-up the test ETL package tonight – any suggestions for me to try would be appreciated.

I also get an LP5 error when I try to open the ETL processor running on the clients server in Malaysia -

This occurred last time I had some changes to make to the package, at that time because it was in the weekend I just reinstalled the ETL software again and it was fine for the next couple of hours that I was working with the system. Now, a couple of weeks later it is giving an LP5 error every time I try to open the ETL Processor on the desktop. However, as before it continues to run correctly every day starting at 03:00 MY time.

The version is 4.6.2.1 – are you aware of any problems that would cause the LP5 with this version?

I guess the first step i s to upgrade the system to 4.2.6.7?

Regards,
Andrew

I can connect remotely and assist you

I think it would be the best

Mike

Hi Mike

I did get the script to run –

The problem was the Result statement –


I changed this line to Result:= true; and the error did not occur and the script finished and the package run completed writing out the file name with the sequence number.

As my last question asked – what environment were you running it in for it to work - the script and package are running in a Windows Server 2000 environment – does that potentially alter the characteristics of what you can have in a script?

I now have to resolve how to get the output as a 4 digit number – the current output is a single character. I have tried searching for some form of formatting that could be applied to the output to the <File_id> variable so that the files name would be MY_Bank0001.csv – having the 4 digit sequence number is critical to pass across to the data warehouse upload routine.

I went looking for some statement that might solve this formatted output but could not find any reference that offered a solution.

I tried a String Function “AddCharLeft” but that did not add the leading characters, nut I am not sure I had the syntax correct.

Can you suggest a method that would output the sequence number as a 4 digit number with leading zeros please?

We have test runs to perform this week for Malaysia, Singapore, Hong Kong, Indonesia, Philippines and Thailand, then roll out the next 5 sites.

Regards,
Andrew

This one should do what you want

SetVariable('<file_id>',LeftPad(IntToStr(i),4,'0'));

I think the confusion here that I did not realise that you were running the calculation From the package. I thought you used transformation.

When you run it from the package you put

result:=true for success and
result:=false for failure

Within the transformation:

you can return anything

result:=true
result:=i;
result:='some string'
Regarding LP5 error
I think we have discussed it several times I suggested to use different anti-virus
Or am I missing something?

Mike