10-21-2014 05:09 AM
I've found probably general issue in ETLs that have SQL join transformation with sub-query ,such ETLs can't be promoted correctly to another servers or even same server due SPK packages from such jobs are damaged.
I reproduced the issue on small test job.
OS - AIX, SAS 9.3.
So correct etls looks like in attached image correctETL.bmp (I erase table names due high security on company
Correct SQL code from SQL join transformation with subquery looks like that:correctSQL.bmp
After import export, or even after simple Copy such ETL to same folder in DI studio, etls demages and looks like that:incorrectETL.bmp
And SQL code loses subquery after IN clause:incorrectSQL.bmp
Export command itself looks like that:
ExportPackage -profile "..." -package "C:\...\package.spk"
So maybe some type of meta-object that needs to be exported omitted(lost), not sure.
It looks like Java/DI studio issue, maybe some hotfix can fix it.
Offcource almost each subquery can be replaced by inner join etc., but anyway - if somebody experienced same error - please share how it can be fixed.
10-21-2014 06:34 AM
It there's any type of issues that should being sent to SAS tech support, this it.
Have you exported manually? In the wizard you could check for dependant objects.
And you are sure that all tables are in the target environment , or part of the package?
10-21-2014 06:42 AM
Thanks for good question.
Yep, I tried to export manually, and all source target table are in target env.
Furthermore,I even tried export to same environment from which I made spk - same result.
Additionaly even if just copy needed ETL and paste it into same folder -creates copy with "Copy of " prefix, and that copy will also has same issue(absolutely same).
So it looks like very similar to some java/DI issue. I tried to make all those steps from a few separate machines, not only mine,-same result.
Suppose you are right and this is question rather for SAS support, but co-workers informed me that they experienced very similar issues on SAS 9.1.3, so I just hope that someone here found out before some nice fix, more clever then just substitude subquery to inner join or smth. like that.