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
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