CODEWITHSUNDEEP
etlinformatica-powercenter
Timur Kutuev
Timur Kutuev

Reputation: 47

Informatica PC restart workflow with different sql query

I am using Informatica PC. I have workflow which have sql query. This query like "select t1, t2, t3 from table where t1 between date '2020-01-01' and date '2020-01-31'" I need to download all data between 2020 and 2022. But I can't write it in query because I will have ABORT SESSION from Teradata. I want to write smth, which will restart workflow with different dates automatically. From first start take 01.2020, second start 02.2020, third start 03.2020 and etc. How can I solve this problem?

Upvotes: 1

Views: 75

Answers (1)

Koushik Roy
Koushik Roy

Reputation: 7387

This is a long solution and can be achieved in two ways. Using only shell script will give you lot of flexibility.

First of all parameterize your mapping with two mapping parameter. Use them in SQL like below.

select t1, t2, t3 from table where t1 between date '$$START_DT' and date '$$END_DT'

Idea is to change them at each run.

  1. Using only shell script - Its flexible because you can handle as many run as you want using this method. You need to call this shell script using some CMD task.
  • Create a master file which has data like this
2020-01-01,2020-01-31
2020-02-01,2020-02-29
2020-03-01,2020-03-31
  • Create three informatica parameter file using above entries. First file(file1) should look like this
[folder.workflow.session_name]
$$START_DT=2020-01-01
$$END_DT=2020-01-31

  • Use file(file1) in a pmcmd to kick off informatica workflow. Pls add --wait so it waits for this to complete.

  • Loop above steps until all entries of master file are complete.

  1. Using informatica only method - This method is not as flexible as above and applicable for only your quesion.
  • Create a shell script that creates three parameter file using above master file.
  • Create three session or three worklets which uses above three parameter files. You need to be careful to use correct parameter for correct session.
  • You can attach those sessions/worklets one after another or in parallel.

Upvotes: 1

Related Questions