Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
chethan_lingaraju
Active Participant
0 Kudos
Hello Readers

Ever ended up in a situation to export each object to a separate ATL file?

Like, you have 500 jobs in a repository and you want to export it to 500 ATL files.

Classic way is to export each object one by one from designer which is error prone and time consuming.

Here is the flow chart for alternate way, which makes use of al_engine:

Well, that is pretty simple. Lets go through each step for further detail.

Reading user inputs:

  1. Get login details of repository
    1. Repository host name
    2. Repository database name
    3. Repository database user name
    4. Repository database Password
  2. Get object type, which can be one of the following.
    1. J - For Jobs
    2. W - For Workflows
    3. D - For Dataflows
    4. F - For Flatfiles
    5. S – Datastores
    6. C - For Custom functions
  3. Get the folder location to store exported files

Loading all the object names of required type:

These are the queries, one of which needs to be executed against the repository database depending on user input.

TypeNameQuery
JJobSELECT NAME FROM [AL_LANG]
WHERE ([OBJECT_TYPE] = 0  AND [TYPE] = 0 )
AND ( [NAME] not in ('CD_JOB_d0cafae2','di_job_al_mach_info'))
WWorkflowSELECT NAME FROM [AL_LANG]
WHERE ([OBJECT_TYPE] = 0  AND [TYPE] = 1 )
AND ( [NAME] not in ('CD_JOB_d0cafae2','di_job_al_mach_info'))
DDataflowSELECT NAME FROM [AL_LANG]
WHERE ([OBJECT_TYPE] = 1  AND [TYPE] = 0 )
AND ( [NAME] not in ('CD_DF_d0cafae2','di_df_al_mach_info'))
FFileformatSELECT NAME FROM [AL_LANG]
WHERE ([OBJECT_TYPE] = 4  AND [TYPE] = 0 )
AND ( [NAME] not in ('di_ff_al_mach_info','Transport_Format'))
SDatastoreSELECT NAME FROM [AL_LANG]
WHERE ([OBJECT_TYPE] = 5  AND [TYPE] = 0 )
AND ( [NAME] not in ('CD_DS_d0cafae2','Transport_Format'))
CCustom functionSELECT FUNC_NAME NAME FROM
[ALVW_FUNCINFO]


Generating al_engine.exe commands

Commands for al_engine should be generated and executed in a loop for every row returned by one of the above listed query.

Example command-line for SQL server repository will look like this:

"%Link_Dir%\bin\al_engine.exe" -NMicrosoft_SQL_Server -passphraseATL -U<SQLUN> -P<SQLPWD> -S<SQLHost> -Q<SQLDB> -Xp@<ObjectType>@<path>\<RepoObject>.atl@<RepoObject>@D

Text within angular brackets are place holders

  • Blue ones are parameters which were provided by user.
  • Red one is loop variable, which is nothing but output of the query.

Windows users can implement it in VB-Script. It served us the best

In case if you don't want to write any code,

You can create simple command generator in excel as shown in the screenshot.

You will need object names before proceeding - which can be easily obtained by executing the given queries.

Formula used in cell B11 is

="""%Link_Dir%\bin\al_engine.exe"" -NMicrosoft_SQL_Server -passphrase" & $B$7 & " -U" & $B$3 & " -P" & $B$4 & " -S" & $B$1 & " -Q"&$B$2 & " -Xp@" & $B$5 & "@""" & $B$6 & "\" & A11 & ".atl""@" & A11 & "@D"

Copy all the generated commands and paste it in command prompt. Objects will be exported one by one to their individual ATL files.

Hope this helps you saving your time and effort.

Cheers

3 Comments
Labels in this area