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

Seven Stars

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

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

                             

Forteen Stars

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

@AndrewSmith1182 ,you can use the below mentionedUntitled.png way that it will create a file it there is data.

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Employee

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

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 :-)

 

 

 

Employee

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

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

 

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 :-)

Seven Stars

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

@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.

 

thashoutput.PNG

Seven Stars

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

@nikhilthampi 

 

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

 

Forteen Stars

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

@AndrewSmith1182 ,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.

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Employee

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

@AndrewSmith1182 

 

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 :-)

Seven Stars

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

@nikhilthampi 

 

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

Seven Stars

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

@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

 

Forteen Stars

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

@AndrewSmith1182 ,I suggest you to give some table name,which is there as part of that view,it should not through null pointer exception.

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog