Skip to Content

Export each object to individual ATL

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:

ObjectsExtractor.jpg

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.

Type Name Query
J Job SELECT NAME FROM [AL_LANG]
WHERE ([OBJECT_TYPE] = 0  AND [TYPE] = 0 )
AND ( [NAME] not in (‘CD_JOB_d0cafae2′,’di_job_al_mach_info’))
W Workflow SELECT NAME FROM [AL_LANG]
WHERE ([OBJECT_TYPE] = 0  AND [TYPE] = 1 )
AND ( [NAME] not in (‘CD_JOB_d0cafae2′,’di_job_al_mach_info’))
D Dataflow SELECT NAME FROM [AL_LANG]
WHERE ([OBJECT_TYPE] = 1  AND [TYPE] = 0 )
AND ( [NAME] not in (‘CD_DF_d0cafae2′,’di_df_al_mach_info’))
F Fileformat SELECT NAME FROM [AL_LANG]
WHERE ([OBJECT_TYPE] = 4  AND [TYPE] = 0 )
AND ( [NAME] not in (‘di_ff_al_mach_info’,’Transport_Format’))
S Datastore SELECT NAME FROM [AL_LANG]
WHERE ([OBJECT_TYPE] = 5  AND [TYPE] = 0 )
AND ( [NAME] not in (‘CD_DS_d0cafae2′,’Transport_Format’))
C Custom function SELECT 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.

Capture.PNG

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
You must be Logged on to comment or reply to a post.
    • Global/local variables, scripts & conditional gets exported as a part of job. System configurations can be exported as a whole by passing object type as p. Never felt a reason to export them to individual file. I haven’t figured out a way yet.

      ABAP dataflow also gets exported when object type D is selected.

      Substitution parameters can be exported as a whole when object type v is passed.

      If its for just viewing individually, a workaround is explained here Export substitution parameters to CSV.

  • Hi ,

     

    Its been long time this discussion is done  but as my question is related to this topic posting now.

    Thanks for providing information to export individual .atl file .

    Could you please let me know to export the repository to atl file to any shared folder using command script.

     

    Thanks in advance!