SQL Archives - File transfer tool from Limagito

24 Sep
How to exit rule when SQL record count is 0
How to exit rule when SQL record count is 0
Q: We have an SQL as source job that emails the results of the SQL statement as a file. The problem is that we do not want the results/email sent when there are no records. I see that the variable %QSC contains the record count. How can I use that variable to exit the rule and not email the empty results file?
A: So I guess you have SMTP as Destination?
Please add the following SQL ‘On Destination’ script and try once more:
Begin psExitCode:= 1; // ... add your code here If psQSC = 0 Then psExitCode:= 0; End.
Feedback customer: Yes, it is a SMTP destination. Based off what you sent below I added the Pascal script with the addition of some logging:
This works as expected and what we want, but it also creates the temp file. Is there a way the temp file can be suppressed? If not, I can add a rule to delete them.
8/9/2023 5:07:43 PM RecordCount: 0
8/9/2023 5:07:43 PM FieldDefs: “contributor_system”,”logged_datetime”,”mrn”,”patient_name”,”fin”,”error_status”,”error_text”,”message_text”
8/9/2023 5:07:43 PM Copied Cerner_ESI_log_other_2023-08-09.csv to C:\Users\SVCFIL~1\AppData\Local\Temp\LimagitoX\LimagitoX.165\Cerner_ESI_log_other_2023-08-09.csv;110
8/9/2023 5:07:43 PM Backup Successful C:\Users\SVCFIL~1\AppData\Local\Temp\LimagitoX\LimagitoX.165\Cerner_ESI_log_other_2023-08-09.csv to C:\Data\SourceFileBackups\Prod\2023\08\09\Cerner_ESI_log_other_2023-08-09.csv
8/9/2023 5:07:43 PM Compiling..
8/9/2023 5:07:43 PM Compiled Succesfully
8/9/2023 5:07:43 PM psQSC (SQL record count) = 0. Exiting rule.
8/9/2023 5:07:43 PM Total Files Redo, Count: 1 & Size: 110
Reply to customer:
Please try to add
DeleteFile(psFilePath + psFileName);
to the script.
Begin psExitCode:= 1; // ... add your code here If psQSC = 0 Then Begin DeleteFile(psFilePath + psFileName); psExitCode:= 0; End; End.
It seems you are using our Backup option. In your case, we would add the ‘backup’ as a second WIN destination because otherwise you still get the temporary file in your backup folder (instead of using our BACKUP option).
Feedback customer: That got it! Thanks you for your help. We are all good now and the rule is working as we want it to.
If you need any info about this ‘exit rule when SQL record count is 0’ question, please let us know.
#sql #filetransfer #filemanagement
Best regards,
Limagito Team
By Limagito-Team SQL record count, SQL
19 Mar
SQL Source – RecordCount as variable?
Q: With the SQL Source is there a way to access the RecordCount as variable? Would be nice to include it in the subject line of an email Destination. Many thanks!
A: We added two new parameters in version v2022.3.18.0.
%QDC = Query Destination record Count, when using SQL as Destination
%QSC = Query Source record Count, when using SQL as Source
You can use %QDC and %QSC within your email or at other positions where parameters can be used. Some screenshots to get you started.
1. In our example we’ll be using SQL as Source. You can find more information about the setup of ‘SQL as Source’ in our Blog.
2. We’ll be using the new %QSC parameter in an email event:
3. We enabled the ‘On Success’ event and the ‘Enable Mail’ option:
4. In the email setup we used our new %QSC parameter so the RecordCount will be displayed.
4. Received Email:
#SQL #Filetransfer
If you need any info about this ‘RecordCount as variable’ option, please let us know.
Best regards,
Limagito Team
By Limagito-Team SQL record count, recordcount
15 Jan
SQL output trailing delimiter at end of row
Trailing delimiter at end of each row
Q: We are using Limagito File Mover, SQL as source, database is MSSQL.
SQL statement (Source)
select
person_last_name as last_name
, person_first_name as first_name
from
my_table
Common Options
Issue
The value for the delimiter is appended at the end of each row. Is there a way to prevent this from happening? The output file is being automatically imported into a system, and that system does not like that extra delimiter at the end of the file. To explain what I’m talking about, the output to the file is currently like this:
last_name,first_name,
Doe,John,
Smith,Mary,
What we would like to see is this:
last_name,first_name
Doe,John
Smith,Mary
Thank you for any assistance you can give.
A: In version v2021.1.15.0 we added some extra options:
SQL Common options (default Enabled):
Trailing Line Break: Determines whether to add a line Break after the last line or not.
Trailing Line Delimiter: When enabled the value for the delimiter is appended at the end of each row
Please disable ‘Trailing Line Delimiter’. This will remove the delimiter at the end of each row.
If you need any info about this option, please let us know.
Best regards,
Limagito Team
By Limagito-Team SQL delimiter, row, trailing