One Star

How to Validate source data before loading into Target data?

Hi Experts,
How to perform validation checks for source data like excel file before loading correct data into Target like SQL SERVER 2008 database.
With Regards,
Viswanath.
24 REPLIES
One Star

Re: How to Validate source data before loading into Target data?

Hi,
I am getting the following error while deploying to MDM server from studio. Please help me in resolving this issue. I have uploaded the FastInfoset.Jar file into lib/java folder but still getting same error
java.lang.NoClassDefFoundError: org/jvnet/fastinfoset/FastInfosetSource
Seventeen Stars

Re: How to Validate source data before loading into Target data?

@praveens_p
please, open your own post.
regards
laurent
Seventeen Stars

Re: How to Validate source data before loading into Target data?

@svisu87
have a look at tSchemaComplianceCheck for schema validation control :
https://help.talend.com/search/all?query=tSchemaComplianceCheck&content-lang=en
for some more data quality use Talend DQ tools.
regards
laurent
One Star

Re: How to Validate source data before loading into Target data?

Hi experts,
Thank you for your reply.
In my Scenario, I need to validate excel file in Source i.e. file contains 36 columns if file have more or less than 36, then data should not load into target table.
To perform this exercise, what should we do?
With Regards,
Viswanath.
Moderator

Re: How to Validate source data before loading into Target data?

Hi,
From your description, have you checked the component TalendHelpCenter:tFileProperties
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: How to Validate source data before loading into Target data?

is it possible to use tschemacompliancecheck against hive table?
Moderator

Re: How to Validate source data before loading into Target data?

Hi dooncomputer,
What's your job requirement? Could you please give us more information about your requirement? Actually, there is no limitation about the input sources.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: How to Validate source data before loading into Target data?

Hi xdshi,
My scenario given below:
1. data verification between source and target databases
2. Informix is my source, oracle is my target.
3. Data is migrated from informix to oracle.
4. I need to validate the data between these database tables.
5. Send the unmatched rows to excel file
6. How do I do using talend? Need help on this.
Moderator

Re: How to Validate source data before loading into Target data?

Hi smathan,
If we understand your requirement very well, you want to capture the changed data and only load these changed data into excel? If so, with talend open studio product,  you need to compare tables to achieve your goal.
The work flow should be: Target DB-->tMap(make inner join on your input and set the "Catch lookup inner join reject" as true)-->outputexcel
                                     Source DB-->
The output will be the changed data.
Let us know if it works.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: How to Validate source data before loading into Target data?

Hi Xdshi,
Thanks for your response. Let me put my requirement in simple way.
1. I have a table data in informix (let us say t1 is the table and employee_id is the column name and the data from 1 to 10 is present)
2. This data is moved to oracle using some XXXX tool
3. Now i will have to check that in Oracle table (let us say t1_1 is the table and employee_id is the column and the data is moved 1 to 9 only but the 10 is missing in oracle)
4. This missing 10, I would like to capture in the excel.
Please do needful. thank you.
One Star

Re: How to Validate source data before loading into Target data?

Hi Sabrina,
I am trying to connect oracle and that was successful but when I try to retrieve schema in the retrieve schema page- page is keep on loading and i am unable to move to NEXT page. My db is huge too. Are there any simplest way or workaround to handle it? Help appreciated. thank you.
Moderator

Re: How to Validate source data before loading into Target data?

Hi,
Thanks for your response. Let me put my requirement in simple way.
1. I have a table data in informix (let us say t1 is the table and employee_id is the column name and the data from 1 to 10 is present)
2. This data is moved to oracle using some XXXX tool
3. Now i will have to check that in Oracle table (let us say t1_1 is the table and employee_id is the column and the data is moved 1 to 9 only but the 10 is missing in oracle)
4. This missing 10, I would like to capture in the excel.

The solution provided in previous post will achieve your goal:
toracleinput-->tMap ( inner join on your input sources "primary key employee_id" and set the "Catch lookup inner join reject" as true)-->outputexcel
tinformixinput-->


Did you follow up document about:TalendHelpCenterRetrieving table schemas to retrieve schema? Is there any error message printed on console?

Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: How to Validate source data before loading into Target data?

Hi Sabrina,
Thanks for your support. your solution is working. have a nice day.
One Star

Re: How to Validate source data before loading into Target data?

Hi Sabrina,
One more question, let us say i have two informix tables which needs to be joined and then one oracle table join these two tables using tmap and then output in excel. So ihave done one informix table and one oracle table before but i haven't tried two informix table and one oracle table. Please guide me.
Moderator

Re: How to Validate source data before loading into Target data?

Hi,
One more question, let us say i have two informix tables which needs to be joined and then one oracle table join these two tables using tmap and then output in excel. So ihave done one informix table and one oracle table before but i haven't tried two informix table and one oracle table. 

Could you please show us your current job design flow? What's the problem you got?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: How to Validate source data before loading into Target data?

Hi Sabrina,
I have the following scenario, could you please advice.
Informix - schema1-> table1
informix - schema2 -> table2
Need to join these two tables to get columns from both using some common keys. Once this step is completed then compare the result set of the above scenario with another migrated schema (oracle db) ie, one table - which is having columns of (table1 & table2 of informix). Finally bring the rejectedrows as output into excel.
Moderator

Re: How to Validate source data before loading into Target data?

Hi smathan,
From your description, you can use tMap to achieve it also.
The workflow should be:                     tOracleInput
                                                                   | (join migrated schema from oracle and table1 & table2 of informix
Informix (source1)-->tMap(common key)------------------->tMap_2  set the option"catch output reject" as true in tmap)------>tfileoutputexcel
 Informix (Source2)                                                                     

Let us know if it works.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: How to Validate source data before loading into Target data?

Hi Sabrina,
I was trying the same logic just myself and it worked out! Feel very happy that i did it in first time. Our productivity become more after we introduce DI tool in our department. thanks for your response as well.
Moderator

Re: How to Validate source data before loading into Target data?

Hi smathan,
Great it works. Feel free to let us know if there is any further help we can give.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: How to Validate source data before loading into Target data?

Hi Sabrina,
As shown in the attached screenshot, i am unable to add synonym, Could you please assist? I am trying to connect Informix db, so is it issue in talend or any other permission issue in informix db?
Moderator

Re: How to Validate source data before loading into Target data?

Hi,
As shown in the attached screenshot, i am unable to add synonym, Could you please assist? I am trying to connect Informix db, so is it issue in talend or any other permission issue in informix db?

Can you successfully connect informix DB? What's informix DB version you are using?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: How to Validate source data before loading into Target data?

I can connect to informix and i can setup the jobs and all are fine except this issue. I am using informix 11.x version. Could you please assist?
One Star

Re: How to Validate source data before loading into Target data?

Hi,
Could you please update me on this? how to come out of this issue?
Moderator

Re: How to Validate source data before loading into Target data?

Hi,
Could you please also indicate the build version you are using? So far,  informix 11.5 is officially supported  for the 6.2.1 version of Talend product at the time of its release.
Is there any error for you when connect informix and add synonym?
How did you connect Informix db? Did you correctly follow up document about:TalendHelpCenter:Centralizing database metadata?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.