How to use Pre-Load and Post-Load command in Data Services.

In this article we will discuss How to use Pre-Load and Post-Load command in data services.

Business Requirement:  Need to execute two programs before and after transformation. The first program will create or update status to receive data from source to Target system and the second program will publish the post transformed data in Target system. These two program needs to execute before and after transformation.

For this scenario, we can use Pre-Load and Post-Load command. Below the details

What is Pre-Load and Post Load?

Specify SQL commands that the software executes before starting a load or after finishing a load. When a data flow is called, the software opens all the objects (queries, transforms, sources, and targets) in the data flow. Next, the software executes Pre-Load SQL commands before processing any transform. Post-Load command will process after transform.

How to use for our business requirement?

We can use both Pre-Load and Post-Load command to execute program before and after transform, below the steps will explain in details

Right click on target object in Dataflow and press open

The Target object option will be shown as below

Both the Pre Load Commands tab and the Post Load Commands tab contain a SQL Commands box and a Value box. The SQL Commands box contains command lines. To edit/write a line, select the line in the SQL Commands box. The text for the SQL command appears in the Value box. Edit the text in that box.

To add a new line, determine the desired position for the new line, select the existing line immediately before or after the desired position, right-click, and choose Insert Before to insert a new line before the selected line, or choose Insert After to insert a new line after the selected line. Finally, type the SQL command in the Value box. You can include variables and parameters in pre-load or post-load SQL statements. Put the variables and parameters in either brackets, braces, or quotes.

To delete a line, select the line in the SQL Commands box, right click, and choose Delete.

Open Post-Load Tab and write post transformation command as same ad Pre-Load

Save and execute. The job will execute Pre-Load, Transform and Post-Load in a sequence.

Data processing successfully completed as per Business requirement.

Note:

Because the software executes the SQL commands as a unit of transaction, you should not include transaction commands in Pre-Load or Post-Load SQL statements.