Hi all,
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"
-objects "/.../.../test(job)
-subprop
-types
"Condition,BusinessRuleFlow,ExternalFile,Cube,SearchFolder,Table,GeneratedTransform,OLAPSchema,InformationMap.OLAP,Measure,Column,Job.CubeBuild,Action,Library,MiningResults,Hierarchy,DeployedJob,CalculatedMeasure,InformationMap.Relational,RootFolder,Document,ConditionActionSet,Prompt,Dimension,Note,StoredProcess,PromptGroup,Job,MessageQueue,Service.SoapGenerated,Level,SharedDimension,DeployedFlow"
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.
Thanks!