It looks like you need to use a third party scheduler to achieve this. However you can still check if at least one of the 2 requirements is possible with Informatica scheduler.
This is not directly possible to configure in the PowerCenter workflow scheduler as far as I know.
However it is possible to achieve such a scheduling without using a 3rd party scheduler.
You would need to create some logic in the workflow itself or create a scheduling workflow with this logic in it and a subsequent command task to start the workflow (using pmrep startworkflow).
Easy way to create this logic is a query on a calendar table which results in returning one row on the desired run dates and use something like $<previous_session_name>.SrcSuccessRows > 0 in the connection to the next task.
And of course the workflow which holds this logic should be scheduled every day of the month using the workflow scheduler.
I haven't used PowerCenter for a while, but if I remember well, what you ask is not possible with a single schedule.
In the past, when I needed complex schedules, I had to build a workflow with decision tasks to evaluate the current date. That workflow was executed daily and when condition was met, then the second workflow was triggered from a command task.
Hope that helps,
It is possible with a little twist.
Schedule the workflow to run every day.
Create a new mapping which checks whether "today" is the first day of a month or a Wednesday. This can be done e.g. using the following expression term:
IIF( To_Char( SessStartTime, 'DD') = '01' OR To_Char( SessStartTime, 'D') = '3', TRUE, FALSE)
(and yes, this can be abbreviated like this:
To_Char( SessStartTime, 'DD') = '01' OR To_Char( SessStartTime, 'D') = '3'
Now, if this value is TRUE, forward one dummy record to some dummy target. On Unix/Linux I would write the dummy record to file /dev/null , on Windows you can, for example, use $PMTargetFileDir\NUL . This way you don't waste any disk space for this dummy output.
And if the value is FALSE, do NOT output this dummy record (easily done using a Filter transformation with this value as its filter condition).
Finally set up a workflow link from the session running this mapping to the "real" first session in your workflow. Set the workflow link condition like this (assuming you named the new first session s_m_CheckDate):
$s_m_CheckDate.Status = SUCCEEDED AND $s_m_CheckDate.TgtSuccessRows > 0
The workflow will run every day, but the "real" work sessions will - thanks to the workflow link condition - only run on each Wednesday and the first day of the month.
Is your question resolved?
If so, please close this thread.
you can achieve this functionality creating two workflows with same mapping sessions with different session log file names, because scheduling the same workflow for two schedule runs is not possible.
This is similar to the concept Nico Heinze explained earlier without any coding in the workflow but with simple to configure.
1. wkf_RUN_WED_WEEK --> Schedule this to run every week Wednesday forever.
2. wkf_RUN_FIRST_MONTH --> schedule this on to run every month 1st of the day.
Hope this helps to achieve data loads using the desired schedule without any issue to the business.
Ignore my post if this is no longer an issue at this time.
Ravindra Babu Nimmagadda
Nico's suggestion is good (albeit with a minor error), but it can be simplified further.
You can skip creating the dummy mapping altogether and just use a link condition from the Start task to the first task of the workflow. (If you have multiple initial tasks, add an empty decision or command task before them.)
TO_CHAR(SYSDATE, 'D') = '4' OR TO_CHAR(SYSDATE, 'DD') = '01'
Note that TO_CHAR(date, 'D') is documented as returning "Day of week (1-7), where Sunday equals 1." So Wednesday is '4'.
Here's an example of the start of a workflow where we've done a similar thing to ensure the workflow only runs between certain hours:
You could equally well use WorkflowStartTime instead of SysDate, the difference is inconsequential as long as you're not starting your workflows almost exactly at midnight (don't do that).