40tv6CDu

1>  at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
2   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
3   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
4   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
5   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
6   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
7   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
8   at ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)
9   at UserComponent.Input0_ProcessInput(Input0Buffer Buffer)
10   at UserComponent.ProcessInput(Int32 InputID, String InputName, PipelineBuffer Buffer, OutputNameMap OutputMap)
11   at Microsoft.SqlServer.Dts.Pipeline.ScriptComponent.ProcessInput(Int32 InputID, PipelineBuffer buffer)
12   at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)
13	
14SELECT Id, source, starttime, endtime, message 
15FROM dbo.sysssislog
16WHERE event = 'onerror' AND ID NOT IN (SELECT ISNULL(cast(LogId as int), 0)  FROM [Client].[EximLog])
17	
18SET ANSI_NULLS ON
19GO
20SET QUOTED_IDENTIFIER ON
21GO
22
23CREATE PROCEDURE [dbo].[spEximLog]
24--Add parameters for the stored procedure
25    @pSystemID nvarchar(max) = NULL,
26    --@sEvent nvarchar(max) = NULL,
27    @pSource nvarchar(max) = NULL,
28    @pStarttime nvarchar(max) = NULL,
29    @pEndtime nvarchar(max) = NULL,
30    @pMessage nvarchar(max) = NULL
31    WITH EXECUTE AS OWNER
32AS
33BEGIN
34    SET NOCOUNT ON;
35
36    DECLARE @vJoboutcome nvarchar(max);
37    DECLARE @vGUID nvarchar(40);
38    DECLARE @vTimeStamp timestamp;
39    DECLARE @vEximID int;
40    DECLARE @vDefaultOperation nvarchar(40);
41    DECLARE @vDefaultEventRegistrationId int;
42    DECLARE @vDefaultVersion int;
43
44    SET @vJoboutcome = 'F';
45    SET @vDefaultOperation = 'Insert';
46    SET @vDefaultEventRegistrationId = 1;
47    SET @vDefaultVersion = 1;
48
49
50    IF NOT EXISTS (SELECT * FROM [dbo].[sysssislog] WHERE ID = @pSystemID)
51
52    BEGIN
53
54        SET @vGUID = CAST((SELECT NEWID()) as nvarchar(40));
55
56        --Inserting 8 cloumns into Exim table
57        INSERT INTO [Client].[EXIMLog]
58        (
59          [JobName], [JobMessage], [JobOutCome], [LogID], [DtmJobStart], [DtmJobEnd]
60        , [EventRegistrationId], [ObjectId]
61        )
62
63        VALUES(@pSource, @pMessage, @vJoboutcome, @pSystemID, @pStarttime, @pEndtime
64        , @vDefaultEventRegistrationId, @vGUID);
65
66        SET @vEximID = (SELECT @@IDENTITY); /*Same EximId is used in Malvern import and in its history table*/
67
68        --Stores the timestamp for the row using where clause
69        SET @vTimeStamp = (SELECT [TimeStamp] from [Client].[EXIMLog] where Id = @vEximId);
70
71
72        --Inserting 12 cloumns into Exim History table
73        INSERT INTO [Client].[EXIMLogHistory]
74        (
75          [Id], [Version], [JobName], [JobMessage], [JobOutCome], [LogID], [DtmJobStart]
76        , [DtmJobEnd], [EventRegistrationId], [ObjectId], [Operation], [TimeStamp]
77        )
78        VALUES(@vEximID, @vDefaultVersion, @pSource, @pMessage, @vJoboutcome, @pSystemID,  @pStarttime, @pEndtime, @vDefaultEventRegistrationId, @vGUID
79        , @vDefaultOperation, CAST(@vTimeStamp as varbinary));
80    END
81END;
82GO
83	
84#region Namespaces
85using System;
86using System.Data.SqlClient;
87using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
88using Microsoft.SqlServer.Dts.Runtime.Wrapper;
89#endregion
90	
91public override void Input0_ProcessInputRow(Input0Buffer Row)
92{
93    System.Data.SqlClient.SqlConnection conn =
94        (System.Data.SqlClient.SqlConnection)Connections.SecondConnection.AcquireConnection(null);
95    System.Data.SqlClient.SqlCommand cmd = new
96        System.Data.SqlClient.SqlCommand("Exec [dbo].[spEximLog]'" + Row.Id + "', '" + Row.source + "', '" + Row.starttime +"', '" + Row.endtime + "' , '" + Row.message + "'", conn);
97    cmd.ExecuteNonQuery();
98}