Scenario:
I need to build custom Logging for our all SSIS Packages. We want to insert some information in a table at start of the SSIS Package and then at the end we want to update the record with "Package Completed successfully" or "Failure".
Solution:
To perform above Task ,we need to have a table first. Here is table definition
CREATE TABLE [dbo].[PkgLog]( [PkgExecKey] [bigint] IDENTITY(1,1) NOT NULL, [PackageName] [varchar](100) NOT NULL, [UserName] [varchar](50) NULL, [StartDate] [datetime] NOT NULL, [EndDate] [datetime] NULL, [Status] [varchar](50) NULL)
We will have the Execute SQL Task the very first Task in our SSIS Package that will insert the information in this table. The EndDate and Status will be left blank and they will be populated at the end. We need to write a Stored Procedure that can insert the row in above table and get the PkgExecKey so we can use that at the end of SSIS Package to update the record for EndDate and Status of Execution.CREATE PROCEDURE [dbo].[InsertPkgLog] @PackageName VARCHAR(100) ,@UserName VARCHAR(100) ,@StartDate DATETIME,@PkgExecKey INT output
AS BEGIN INSERT INTO [dbo].[PkgLog] ([PackageName] ,UserName ,[StartDate]) VALUES (@PackageName ,@UserName ,@StartDate ) SET @PkgExecKey=SCOPE_IDENTITY() END
If you notice that the @PkgExecKey is output type parameter in our above Stored Procedure. After inserting the record in table, the variable will be holding the identity value. We will save this value to our SSIS variable and then at the end of Package we will use this to update the table column values for EndDate and Status.
Let's create SSIS Variable call PkgExecKey as shown below
Fig 1: Create Variable in SSIS Package
Now bring Execute SQL Task to Control Flow Pane and configure as shown below
Fig 2: Configure Execute SQL Task to get output Parameter from Stored Procedure to SSIS Variable
As shown in fig 2. You need to provide the Connection Manager that is pointing to Database which has the Stored Procedure. You will be writing the statement Exec dbo.InsertPkgLog ?,?,?,?output. Each "?" is to map single parameter. If it is input parameter you will be using "?" but for output parameter you have to write "? output".
Now go to Parameter Mapping and map the parameters to the SSIS Variables.
Fig 3: Map Stored Procedure Parameters ( input and output) to SSIS Variables
Once Execute SQL Task will execute, the Stored Procedure will execute by using input parameters and return value for output parameter(PkgExecKey) and save into SSIS Variable ( PkgExecKey). You can use this variable after different tasks in your SSIS Package. This value can be used to update the dbo.PkgLog Table record.
Let's update the record at the end of package by using PkgExecKey as shown below. I have written a Stored Procedure for this with below definition.
CREATE PROCEDURE [dbo].[UpdatePkgLog] @Status VARCHAR(10) ,@PkgExecKey INT AS BEGIN UPDATE dbo.PkgLog
SET EndDate=GETDATE() ,Status=@Status WHERE PkgExeckey=@PkgExecKey END
Fig 4: Updating record by using Stored Procedure in Execute SQL Task
As we can see in fig 4, we are passing two input parameters 'Success' as parameter one and ? that we will map to PkgExecKey variable.
Fig 5: Map input parameter to Stored Procedure In Execute SQL Task.
In this post we learnt how to use Stored Procedure with input and output parameter in Execute SQL task and how to map input and out parameters of Stored Procedure to SSIS Variables. I hope this will help :)
iso registration in delhi
ReplyDeleteiso 22000 certification cost
ISO 9001 Certification in Noida
website designing services
SEO Service Consultant
IEEE Final Year projects Project Centers in India are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes, while specialists like the enjoyment in interfering with innovation. For experts, it's an alternate ball game through and through. Smaller than expected IEEE Final Year project centers ground for all fragments of CSE & IT engineers hoping to assemble. Final Year Projects for CSE It gives you tips and rules that is progressively critical to consider while choosing any final year project point.
DeleteJavaScript Online Training in India
JavaScript Training in India
The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training
iso certification in noida
ReplyDeleteiso certification in delhi
ce certification in delhi
iso 14001 certification in delhi
iso 22000 certification in delhi
iso consultants in noida
we have provide the best ppc service.
ReplyDeleteppc company in gurgaon
website designing company in Gurgaon
PPC company in Noida
seo company in gurgaon
PPC company in Mumbai
PPC company in Chandigarh
Digital Marketing Company
Shweta gaur is one of the famous makeup artist in all over India. We are providing the best makeup artist courses and more other courses in over branches in Delhi.
ReplyDeleteMakeup Artist in Delhi
Makeup Artist
Best Makeup Artist in Delhi
Best Makeup Artist in East Delhi
Top Makeup Artist in Delhi
Top Makeup Artist in India
Bridal Makeup
Through this post, I know that your good knowledge in playing with all the pieces was very helpful. I notify that this is the first place where I find issues I've been searching for. You have a clever yet attractive way of writing on Msbi online training
ReplyDeleteMsbi online training Hyderabad
Msbi online training India
Msbi online course
Msbi course
Msbi training
Msbi certification training
Thank you so much for highlighting the fact about SSIS and other related operations that make up such tools of prominence.
ReplyDeleteSSIS Postgresql Write
Đặt vé máy bay tại Aivivu, tham khảo
ReplyDeletevé máy bay đi Mỹ bao nhiêu
vé máy bay huế đi sài gòn giá rẻ
giá vé máy bay đi hà nội tháng 12
đi máy bay ra đà lạt
giá vé máy bay đi Huế
giá taxi đi sân bay nội bài
combo du lịch đà nẵng hội an
Thanks For Your Blog on MSBI Certification
ReplyDeletePretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I'll be subscribing to your feed and I hope you post again soon. Big thanks for the useful info. https://www.buyyoutubesubscribers.in/
ReplyDeleteBrilliant post, checkout this as well:- algebra tutoring online
ReplyDeleteWow, cool post. I'd like to write like this too - taking time and real hard work to make a great article... but I put things off too much and never seem to get started. Thanks though. video conferencing platform Whoosh
ReplyDelete