- are you using versioning? If yes, please check if everything is checked in (Versoning -> Find Checkouts). Afterwards make a refresh mapping and compare the override from the session with the override from qualifier.
- Check if the query sent to database is identical for your runs. If not, please do a "Refresh mapping" in the session, save, check in.
Is it the consistent behavior OR happened just once ?
Did you try updating the schedule once and then checked ?
This is definitely consistent. It's been scheduled for the past 30-45 days and it has only generated rows on 2-3 occasions.
I have not changed the schedule of the workflow. I have Stopped and Unscheduled and Rescheduled it many times.
I have also deleted the session from the workflow and re-added it.
We are not using versioning.
I have a request out to our DBA team to see if they can confirm whether or not the query is actually hitting the database at the time the workflow/session runs.
Also, does the workflow produce / use a parameter file which names the connection (resp. connection details like password and the like) for this session? Or even a parameterised connection? That might explain this behaviour.
Please check the session logs from a manual start and from the scheduled run at night.
yes, the workflow uses a parameter file. There are 16 other sessions in this workflow using the same parameter file and same connections and all of them are running properly. This is the only session that is not producing rows out of the SQ.
try to get the session log in Verbose Data.
That way you will get more information about the SQL query which you can copy and confirm if it is working.
I tried with both Verbose Data and Verbose Initialization (one in Dev and one in Prod) and that doesn't give me any more insight. Just says there are no rows from the SQ.
Can you share the below things:
- Session log in verbose when session runs on schedule and does not read any data.
- Session log when run manually.
- IS log from the same date/timeframe.
- What if you don't use SQL Override if at all tried, do you the same behavior on scheduled run ?
Could you please double-check (e.g. with the help of the DBAs) whether during the session working fine and during the session delivering no data the same DB user with the same connection details (DB name, schema, and so on) are used by PowerCenter? When a scheduled workflow and a session started "manually" deliver different sets of data, the only way how this can happen I've so far are differences in the DB connection.
Agree with the posts above . This needs more investigation from your DBA end , to see if the same query is issued to the DB at two different times then why is it returning records one time and not the other .
So according to the DBA's, they don't see the query hitting the database when the workflow runs on its nightly schedule.
They DO see the query hitting the database when I run the session manually.
Yesterday, I created a new workflow and put this session in it and it ran perfectly last night scheduled at 11:30pm EST.
This happened both in Dev and Prod.
So this session doesn't seem to be actually sending the query to the database in one workflow but it sends it in another.
I'm not sure where to go from here. While technically a workaround, I would really rather not have to use a completely separate workflow for one session.
Thats strange . Which version of SQl server is this ? Also are you using ODBC to connect to it or OLEDB
ODBC but not using a DSN on the Informatica host box.
SQL Server version: 13.0.5830.85