I have created Logging under SSIS and it's generated in the system table of my database as [dbo].[sysssislog]
. I am trying to get specific columns like Id, Source, starttime, endtime and message to another SQL Server table, client.EximLog and Client.EximLogHistory
.
Every time I run the package it's throws following error:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at ScriptMain.Input0_ProcessInputRow(Input0Buffer Row) at UserComponent.Input0_ProcessInput(Input0Buffer Buffer) at UserComponent.ProcessInput(Int32 InputID, String InputName, PipelineBuffer Buffer, OutputNameMap OutputMap) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponent.ProcessInput(Int32 InputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)
I have created a event handler 'Onerror' to execute a dataflow task, say 'Connect to SSIS Log Table'.
In the dataflow task, I am connecting to the sysssislog table using a OLD DB Connection.
SELECT Id, source, starttime, endtime, message
FROM dbo.sysssislog
WHERE event = 'onerror' AND ID NOT IN (SELECT ISNULL(cast(LogId as int), 0) FROM [Client].[EximLog])
I have connected the OLD DB source connection to Script component as Destination. I am Executing the following stored procedure.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spEximLog]
--Add parameters for the stored procedure
@pSystemID nvarchar(max) = NULL,
--@sEvent nvarchar(max) = NULL,
@pSource nvarchar(max) = NULL,
@pStarttime nvarchar(max) = NULL,
@pEndtime nvarchar(max) = NULL,
@pMessage nvarchar(max) = NULL
WITH EXECUTE AS OWNER
AS
BEGIN
SET NOCOUNT ON;
DECLARE @vJoboutcome nvarchar(max);
DECLARE @vGUID nvarchar(40);
DECLARE @vTimeStamp timestamp;
DECLARE @vEximID int;
DECLARE @vDefaultOperation nvarchar(40);
DECLARE @vDefaultEventRegistrationId int;
DECLARE @vDefaultVersion int;
SET @vJoboutcome = 'F';
SET @vDefaultOperation = 'Insert';
SET @vDefaultEventRegistrationId = 1;
SET @vDefaultVersion = 1;
IF NOT EXISTS (SELECT * FROM [dbo].[sysssislog] WHERE ID = @pSystemID)
BEGIN
SET @vGUID = CAST((SELECT NEWID()) as nvarchar(40));
--Inserting 8 cloumns into Exim table
INSERT INTO [Client].[EXIMLog]
(
[JobName], [JobMessage], [JobOutCome], [LogID], [DtmJobStart], [DtmJobEnd]
, [EventRegistrationId], [ObjectId]
)
VALUES(@pSource, @pMessage, @vJoboutcome, @pSystemID, @pStarttime, @pEndtime
, @vDefaultEventRegistrationId, @vGUID);
SET @vEximID = (SELECT @@IDENTITY); /*Same EximId is used in Malvern import and in its history table*/
--Stores the timestamp for the row using where clause
SET @vTimeStamp = (SELECT [TimeStamp] from [Client].[EXIMLog] where Id = @vEximId);
--Inserting 12 cloumns into Exim History table
INSERT INTO [Client].[EXIMLogHistory]
(
[Id], [Version], [JobName], [JobMessage], [JobOutCome], [LogID], [DtmJobStart]
, [DtmJobEnd], [EventRegistrationId], [ObjectId], [Operation], [TimeStamp]
)
VALUES(@vEximID, @vDefaultVersion, @pSource, @pMessage, @vJoboutcome, @pSystemID, @pStarttime, @pEndtime, @vDefaultEventRegistrationId, @vGUID
, @vDefaultOperation, CAST(@vTimeStamp as varbinary));
END
END;
GO
Following is the script I am using in the Script Component and the connection manager is an ADO.net Connection.
#region Namespaces
using System;
using System.Data.SqlClient;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
#endregion
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
System.Data.SqlClient.SqlConnection conn =
(System.Data.SqlClient.SqlConnection)Connections.SecondConnection.AcquireConnection(null);
System.Data.SqlClient.SqlCommand cmd = new
System.Data.SqlClient.SqlCommand("Exec [dbo].[spEximLog]'" + Row.Id + "', '" + Row.source + "', '" + Row.starttime +"', '" + Row.endtime + "' , '" + Row.message + "'", conn);
cmd.ExecuteNonQuery();
}