Working with Pre-Load and Post-Load commands in Target table

Hello Readers,

We can use Pre-load and post-load commands present in the Target table properties tabs

These two works in opposite way to each other, commands written in Pre-load will execute before the DS Job starts executing.

Commands written in Post-Load will execute after the finishing of the DS job.

I will show you how to work with these commands by executing Stored Procedures written in SQL.

1. Writing a stored procedure to fetch a record from one table to another empty table

1.Open SQL Server management Studio.

2.Connect to a database

3.Expand to Programmability folder-> Storedprocedures

4.Right click on the StoredProcedures and select New StoredProcedures option

5.Now write a storedprocedure to extact a record from one table and insert into another empty table(source of DF)

My Pre-Load storedProcedure is like this

USE [NORTHWND]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[spINSERT_dbo_Preloaded]

AS

BEGIN

  insert into [NORTHWND].[dbo].Preloadedtable([CategoryID],[CategoryName],[Description],[Picture]) select * from [NORTHWND].[dbo].[Categories] where CategoryID=1;

END

through this sp, a record from categories table is inserted in Preloadedtable.

My Post-Load SP is

USE [NORTHWND]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[spINSERT_dbo_Preloadedtable]

AS

BEGIN

  insert into [NORTHWND].[dbo].Preloadedtable([CategoryID],[CategoryName],[Description],[Picture]) select * from [NORTHWND].[dbo].[Categories] where CategoryID=4;

END

This sp will insert another record with categroryID 4

Now will start designing the dataflow in DS Designer

The flow of the DF is like this

1.Attach the source table onto the Dataflow.

2.Map the source columns to the target table using Query transform

3.attach the target table to the query transform

check the source and target table data

before executing the DF it will be zero records in both the tables

Open the Pre-Post table and navigate to pre-load and post load commands tabs

Enter the sql commands in both pre-load and post-load to execute the storedprocedures written in sql server

1.

2.

validate the DF and execute the DF

based on the SP written, the source will contain 2 records, 1 at the time of pre-load and 1 after the execution of the DF flow completely till the target table.

as per the mapping specified and conditions mentioned in the query transform the data will be transferred to target table.

this is how we can use the Pre-load and Post-load commands in the target table.

more blogs coming soon..with spl concepts…

Thanks

PrasannaKumar.P