ETL getting Errored out
I am facing a very strange issue.
I am trying to run a ETL in UAT environment but it is getting failed with error (table or view does not exist) , I checked manually and tables exists in both source and target.
Target Data store is pointing to UAT DB and Source is pointing to UAT DB
Then I tried ny pointing the source to SIT and target to UAT and vice versa, it worked.
Not sure why data services is behaving strangely.
Dirk Venken replied
Check the generated SQL for running in UAT. It is a full pushdown, an insert with a subselect, isn't it?
You have source and target tables in different schema, and target owner does not have privileges on source. Hence the error message.
When you run the same data flow over two different databases, DS will not generate a full pushdown (unless you define database links). Data will be retrieved from source schema with source credentials, pulled thru DS memeory and then inserted into target schema with target credentials.