Problem With Quotes in @[System::ErrorDescription] Variable

I am using an Execute T-SQL Task as a part of an OnError event Handler in my SSIS Package. When occurs an error, using the Expressions-feature, my Execute T-SQL task builds an Insert Statement to insert the @.System::ErrorDescription into a table.

"
INSERT INTO [ErrorDB].[dbo].[ISErrors]
([EventType]
,[PackageName]
,[TaskName]
,[DateDone]
,[Status]
,[Host]
,[ErrorCode]
,[ErrorDescription]
,[Comments])
VALUES
( 'OnError'
, '"+ @.[System::PackageName] + "'
, '"+ @.[System::SourceName] + "'
,getdate()
,'Failed'
,'" + @.[System::MachineName] + "'
, null
, '" + @.[System::ErrorDescription] + "'
,null
)

"

When I run the task ( not the package, only the task) everything is ok ( since the ErrorDescription variable is empty)

But when an error occurs in my package, then the T-SQL task fails giving the following error

[Execute SQL Task] Error: Executing the query " INSERT INTO [LogDB].[dbo].[ISFullMaintenanceErrors] ([EventType] ,[PackageName] ,[TaskName] ,[DateDone] ,[Status] ,[Host] ,[ErrorCode] ,[ErrorDescription] ,[Comments]) VALUES ( 'OnError' , 'Package' , 'TrialTempEx' ,getdate() ,'Failed' ,'SCYLLA' , null , @.[System::ErrorDescription] ,null ) " failed with the following error: "Must declare the scalar variable "@.".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I realized that the problem is that the @.[System::ErrorDescription] contains quotes ( " ' ) and this is the reason that the insert statement fails. I tried the replace function but there was no solution

Any help would be appreciated

For this very reason I advocate using the parameter support in the Exec SQL Task, over expressions for this type of statement. This is basically a SQL injection attack, albeit benign, but by using a parameterised statement, you can protect yourself from this. The other issue you may hit is with long descriptions, you could exceed the 4000 character limit for an expression result.|||

Darren and I don't exactly see eye-to-eye on this one but I'll concede he makes a good, if slightly dramatic, point about SQL Injection

If you do want to carry on using expressions then you can just wrap the variable in a REPLACE() function.

-Jamie