BookmarkSubscribeRSS Feed
Yura2301
Quartz | Level 8

 

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!


incorrectSQL.bmpcorrectETL.bmpincorrectETL.bmpcorrectSQL.bmp
2 REPLIES 2
LinusH
Tourmaline | Level 20

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?

Data never sleeps
Yura2301
Quartz | Level 8

Hi Linus,

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.

Thanks!

suga badge.PNGThe SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment. 

Join SUGA 

CLI in SAS Viya

Learn how to install the SAS Viya CLI and a few commands you may find useful in this video by SAS’ Darrell Barton.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 1503 views
  • 0 likes
  • 2 in conversation