Using SqlStatementSource property of Execute SQl Task

Question

Post reply

Using SqlStatementSource property of Execute SQl Task

JMI

SSChampion

Points: 11831
More actions
November 10, 2008 at 12:51 pm

#201084

In the Event Handlers window OnError I have "SQL_Error" Execute SQL Task
in which I generate SqlStatementSource Property:
"INSERT stg.Errors(error_code)
VALUES
'' + @[System::ErrorCode] + ''
"
CREATE TABLE (error_code int)
It fails. Something is wrong with the syntax. I suspect
single quotes or double quotes.
In Query Analyzer this query works OK:
INSERT stg.Errors(error_code)
VALUES
(1)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

matzer SSCrazy Eights Points: 9727 More actions · Answer 1

I guess you forgot the parentheses. Try

"INSERT stg.Errors(error_code)

VALUES (

'' + @[System::ErrorCode] + '')

"

Peter

JMI SSChampion Points: 11831 More actions · Answer 2

Peter,

I copied your code

and pasted it to Expression window.

Got error again:

Error: 0xC002F210 at SQL_LogError, Execute SQL Task: Executing the query "INSERT stg.Errors(error_code)
VALUES (
'' + @[System::ErrorCode] + ''
)
" failed with the following error: "Must declare the scalar variable "@".".

You are using two single quotes before and after

@[System::ErrorCode]. Not one double quote. Right?

JMI SSChampion Points: 11831 More actions · Answer 3

I try to simplify the situation to isolate the problem.

Now I just write to error_desc VARCHAR(255) column:

"INSERT stg.Errors(error_desc)
VALUES (
'" + @[System::ErrorDescription] + "'
)
"

but got an error again!

Error: 0xC002F210 at SQL_LogError, Execute SQL Task: Executing the query "INSERT stg.Errors(error_desc)
VALUES (
'SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (305)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (305)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
'
)
" failed with the following error: "The statement has been terminated.".

JMI SSChampion Points: 11831 More actions · Answer 4

It makes me crazy...

This works:

"INSERT stg.Errors(error_desc)
VALUES (
'" + @[System::packageName] + "'
)
"

and this fails:

"INSERT stg.Errors(error_desc)
VALUES (
'" + @[System::ErrorDescription] + "'
)
"

I checked in a Script. ErrorDescription is populated.

Public Class ScriptMain
Public Sub Main()
MsgBox("ErrorDescription=" + Dts.Variables("System::ErrorDescription").Value.ToString())
Dts.TaskResult = Dts.Results.Success
End Sub
End Class

matzer SSCrazy Eights Points: 9727 More actions · Answer 5

Maybe the error description contains characters which breaks the statement. Try parameterizing the statement.

Peter

JMI SSChampion Points: 11831 More actions · Answer 6

You mean building the statement string as a variable

and then select Source as a variable?

matzer SSCrazy Eights Points: 9727 More actions · Answer 7

Note that right now you are using string concatenation to build you query. If @[System::ErrorDescription] contains a single quote, the statement will become invalid, which probably explains the error you got:

...

)

" failed with the following error: "The statement has been terminated.".

What I mean is you have to use a parameter in your query:

INSERT stg.Errors(error_desc) VALUES (?)

On the ParameterMapping tab, add a parameter with variable @[System::ErrorDescription] mapped to parameter name 0 of type (n)varchar. Note that ? as a parameter is connection dependend. It works with SQL Server. Other connection providers may require other notation for parameters. Look up BOL if you are not using SQL Server.

Peter

JMI SSChampion Points: 11831 More actions · Answer 8

I tried what you suggested.

It worked.

But the results I saw are not the ones I expected.

But that's another story.

When I display ErrorDescription in a Script

I see one error descriptions and number of records.

Now with Execute SQL Task

INSET stg.Errors(...)

VALUES (?)

I get another picture.

Don't understand why.

I am testing exactly the same one record.

And the error I expect is CAN NOT INSERT NULL.

Aldo D'Offizi SSC Enthusiast Points: 120 More actions · Answer 9

Damn the event handlers! :°(,

i have a similar problem... but i solved for syntax ...

my problem (great Problem :P) are the "user" variables ...

I released a packages with a lot event handler (for error an postexecute),

for onError Event Handlers i've this SqlStatementSource to a Execute Sql Task (into expression editor) :

"Insert into SSISErrorLog

(Event,Package,Error)

VALUES

('onError','"+ @[System::PackageName]+ "','"+@[User::DTSXError]+"')"

For system variable it's ok ... but i can read User::DTSXError variable (also from Script Task)

how i solve?

pleaz help!

Event Handlers .