yxzNwPzQ

1SELECT Id, source, starttime, endtime, message 
2FROM dbo.sysssislog
3WHERE event = 'onerror' AND ID NOT IN (SELECT ISNULL(cast(LogId as int), 0)  FROM [Client].[EximLog])
4	
5SET ANSI_NULLS ON
6GO
7SET QUOTED_IDENTIFIER ON
8GO
9
10CREATE PROCEDURE [dbo].[spEximLog]
11--Add parameters for the stored procedure
12    @pSystemID nvarchar(max) = NULL,
13    --@sEvent nvarchar(max) = NULL,
14    @pSource nvarchar(max) = NULL,
15    @pStarttime nvarchar(max) = NULL,
16    @pEndtime nvarchar(max) = NULL,
17    @pMessage nvarchar(max) = NULL
18    WITH EXECUTE AS OWNER
19AS
20BEGIN
21    SET NOCOUNT ON;
22
23    DECLARE @vJoboutcome nvarchar(max);
24    DECLARE @vGUID nvarchar(40);
25    DECLARE @vTimeStamp timestamp;
26    DECLARE @vEximID int;
27    DECLARE @vDefaultOperation nvarchar(40);
28    DECLARE @vDefaultEventRegistrationId int;
29    DECLARE @vDefaultVersion int;
30
31    SET @vJoboutcome = 'F';
32    SET @vDefaultOperation = 'Insert';
33    SET @vDefaultEventRegistrationId = 1;
34    SET @vDefaultVersion = 1;
35
36
37    IF NOT EXISTS (SELECT * FROM [dbo].[sysssislog] WHERE ID = @pSystemID)
38
39    BEGIN
40
41        SET @vGUID = CAST((SELECT NEWID()) as nvarchar(40));
42
43        --Inserting 8 cloumns into Exim table
44        INSERT INTO [Client].[EXIMLog]
45        (
46          [JobName], [JobMessage], [JobOutCome], [LogID], [DtmJobStart], [DtmJobEnd]
47        , [EventRegistrationId], [ObjectId]
48        )
49
50        VALUES(@pSource, @pMessage, @vJoboutcome, @pSystemID, @pStarttime, @pEndtime
51        , @vDefaultEventRegistrationId, @vGUID);
52
53        SET @vEximID = (SELECT @@IDENTITY); /*Same EximId is used in Malvern import and in its history table*/
54
55        --Stores the timestamp for the row using where clause
56        SET @vTimeStamp = (SELECT [TimeStamp] from [Client].[EXIMLog] where Id = @vEximId);
57
58
59        --Inserting 12 cloumns into Exim History table
60        INSERT INTO [Client].[EXIMLogHistory]
61        (
62          [Id], [Version], [JobName], [JobMessage], [JobOutCome], [LogID], [DtmJobStart]
63        , [DtmJobEnd], [EventRegistrationId], [ObjectId], [Operation], [TimeStamp]
64        )
65        VALUES(@vEximID, @vDefaultVersion, @pSource, @pMessage, @vJoboutcome, @pSystemID,  @pStarttime, @pEndtime, @vDefaultEventRegistrationId, @vGUID
66        , @vDefaultOperation, CAST(@vTimeStamp as varbinary));
67    END
68END;
69GO
70	
71#region Namespaces
72using System;
73using System.Data.SqlClient;
74using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
75using Microsoft.SqlServer.Dts.Runtime.Wrapper;
76#endregion
77	
78public override void Input0_ProcessInputRow(Input0Buffer Row)
79{
80    System.Data.SqlClient.SqlConnection conn =
81        (System.Data.SqlClient.SqlConnection)Connections.SecondConnection.AcquireConnection(null);
82    System.Data.SqlClient.SqlCommand cmd = new
83        System.Data.SqlClient.SqlCommand("Exec [dbo].[spEximLog]'" + Row.Id + "', '" + Row.source + "', '" + Row.starttime +"', '" + Row.endtime + "' , '" + Row.message + "'", conn);
84    cmd.ExecuteNonQuery();
85}