How to make a DB Component not run based on a table being populated or not in DB

[Anonymous]

I have a Job that writes from MSQL Server DB into CSV Files then sent to a FTP site that runs weekly. Some of my tables don't get populated each week and I don't want to send an empty/blank csv file to the FTP Site. I have this job automated in Windows Task Scheduler, so I cannot just delete the file manually before sending to FTP site. Is there anyway in Talend to put a condition on a tMSSQLDBInput to not generate a CSV file if the table is not populated. Skip through the tMSSQLDBInput components that doesn't have data in the table, without failing the job and still generating the CSVs for tables with that

My job is setup tDBconnection -->  tMSSQLDBInput ---> tMSSQLDBOutputBulk x15 with subjobokay precedence constraint between them

Please advise

Thanks

Andrew

[manodwhb]

@sm ,you can use the below mentioned way that it will create a file it there is data.

[Anonymous]

Hi Andrew,

        The best way is to do a select query in the same MS SQL table to check whether the table is having any new records and assign it to a context variable. If the count of records is zero, you can skip the data extraction part using a Run if condition (by adding whether context.row_count <>0).

        This means that all the files will not be present in the target location. You can check whether a file is available or not using tFileExist component. Again, use a Run if condition to verify this part and skip if a file is not available.

         There are lot of solved scenarios for Run if condition in Talend community itself both for file and DB. This will give you an idea about how to do part. If you are stuck somewhere, let us know with error screenshots.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[Anonymous]

@manodwhb  - Didn't see that you have already replied it

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[Anonymous]

@manodwhb 

I got a Exception in component tDBInput_1 (TestingWrite)
java.lang.NullPointerException. Was this caused by the Table not having any records in it? I don't want the job to fail when there is no records in the table just to skip the write to a CSV and continue on to the next table.

[Anonymous]

@nthampi 

Could you show me an example of this? Assigning a Context Variable to a Query Result and then the syntax for IF precedence constraint? If you can share a link to a similar example I can just look at that. I searched through the forums and didn't see any topics related to this one. Screenshots of your canvas project space with the components would be much appreciated.

Thanks,

Andrew

[manodwhb]

@sm ,can you show me the component settings of on which are you getting null pointer exception? it could be some thing that you nave not configured properly that schema name or table name missing.

[Anonymous]

@sm 

Your tDBInput is generating null pointer exception. This means that one of the value you are assigning in the query section or some of the parameters tDBInput is having null value. Could you please check these details and come back? If you can provide a screenshot of the tDBInput, it would be really great.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[Anonymous]

@nthampi 

The screen shot I had in my previous post is my current configuration. Right now, There is no Data in that Table. Is that why it's throwing a NULL Pointer Exception? My input query is a simple "SELECT * FROM mytable". I want the job to run through all the components still, and I would have a On subjob ok precedence constraint and connect each job after the DBOutputBulk component because I have multiple tables that I am extracted data from.

Let me know if you can show me another example or post that doesn't use the tHash input/output components if that is the issue

[Anonymous]

@manodwhb 

I don't have the Table Name Listed in the tDBInput Component is this required? I am exporting data from a View and Not a Table so that's why I leave the Table Name text box blank in the Component setting.  I have zero data in the View I'm currently testing, is that why it's throwing an NULL Pointer Exception? I want the job to still run through all the components in the job but to not produce a CSV file is the table is empty.

Is this possible?

Please Advise