September 2, 2008 at 2:47 pm
I have a On error event handlers, using Execute sql task.
I have a expression on the task as sqlstatementsource as
"INSERT INTO [dbo].[SSISNMEncounterLog]
([PackageName]
,[EventType]
,[ExecutionID]
,[PackageID]
,[SourceName]
,[SourceID]
,[ErrorCode]
,[ErrorDescription]
,[InteractiveMode]
,[MachineName]
,[UserName]
)
VALUES
('"+ @[System::PackageName] +"'
,'"+ @[System::TaskName] +"'
,'"+ @[System::ExecutionInstanceGUID] +"'
,'"+ @[System::PackageID] +"'
,'"+ @[System::SourceName] +"'
,'"+ @[System::SourceID] +"'
,"+ (DT_STR, 15 , 1252) @[System::ErrorCode] +"
,'"+ @[System::ErrorDescription] +"'
,'"+ (DT_WSTR, 6) @[System::InteractiveMode] + "'
,'"+ @[System::MachineName] + "'
,'"+ @[System::UserName] +"' )"
It works fine for most of the errors. But it fails itself when i get this sorts of the error . the error is
[Execute SQL Task] Error: Executing the query "INSERT INTO [dbo].[SSISNMEncounterLog] ([PackageName] ,[EventType] ,[ExecutionID] ,[PackageID] ,[SourceName] ,[SourceID] ,[ErrorCode] ,[ErrorDescription] ,[InteractiveMode] ,[MachineName] ,[UserName] ) VALUES ('CETOutbound' ,'On Error Log' ,'{77DA370D-B5D9-457F-84DF-833FC62103D1}' ,'{B1CEAC56-6AA7-493C-BF1D-093E89E770F6}' ,'Updatee DRUG_CLM tbl to Status T' ,'{CEE5D68C-FB2F-4BE7-AD63-BD1ADDD2891E}' ,-1073548784 ,'Executing the query "EXEC dbo.uspUpdateDrug" failed with the following error: "Invalid object name 'd'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. ' ,'True' ,'P4825I00' ,'P4825I00\I31108C' )" failed with the following error: "Incorrect syntax near 'd'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
If i execute the task manulally it works fine or if i put invalid objcvet name in the package it works fine. But for some reason it is not working when i get some sorts of errors like above
September 2, 2008 at 3:19 pm
I found the issue , But how to solve it. I know the problem is that when i am inserting into table teh values are encode within ' ' and its treating that as value for another one. so Its telling incorrect syntax.
Do anyone know how to solve it.
Thanks
September 2, 2008 at 4:29 pm
Escape the characters that are causing problems, e.g.
REPLACE( @[System::ErrorDescription],"'","''")
September 2, 2008 at 4:38 pm
Thanks Mukti again, But can u plz replace the value into my expression, the way you want . i have tried but fail
thanks
September 2, 2008 at 4:45 pm
Hey Mukti Got it Finally Thanks Again..............
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply