SSIS: Custom Logging Using Event Handlers

  • hi

    im trying to create an event lof for when thinks fail in my ssis package

    im using the following code

    INSERT INTO [dbo].[SSISLog]

    ([EventType]

    ,[PackageName]

    ,[TaskName]

    ,[EventCode]

    ,[EventDescription]

    ,[PackageDuration]

    ,[ContainerDuration]

    ,[InsertCount]

    ,[UpdateCount]

    ,[DeleteCount]

    ,[Host])

    VALUES

    (

    'OnPostExecute',

    '"+@[System::PackageName]+"',

    '"+@[System::TaskName]+"',

    0,

    '"+@[System::SourceDescription]+"',

    "+ (DT_STR, 6, 1252)DATEDIFF("ss", @[System::StartTime] , GETDATE() ) + ",

    "+ (DT_STR, 6, 1252)DATEDIFF("ss", @[System::ContainerStartTime] , GETDATE() ) + ",

    411,

    2,

    12,

    '"+ @[System::MachineName] +"'

    )

    getting the following error

    Msg 102, Level 15, State 1, Line 21

    Incorrect syntax near 'ss'

    anyone any ideas

  • You're muddling up your single and double quotes. Use single quotes (') when quoting something in T-SQL, and double quotes (") when quoting something in an SSIS expression.

    John

  • hi

    thanks for reply it will be going into an ssis package

    so does it go in like this

    INSERT INTO [dbo].[SSISLog]

    ([EventType]

    ,[PackageName]

    ,[TaskName]

    ,[EventCode]

    ,[EventDescription]

    ,[PackageDuration]

    ,[ContainerDuration]

    ,[InsertCount]

    ,[UpdateCount]

    ,[DeleteCount]

    ,[Host])

    VALUES

    (

    'OnPostExecute',

    "+@[System::PackageName]+",

    "+@[System::TaskName]+",

    0,

    "+@[System::SourceDescription]+",

    "+ (DT_STR, 6, 1252)DATEDIFF("ss", @[System::StartTime] , GETDATE() ) + ",

    "+ (DT_STR, 6, 1252)DATEDIFF("ss", @[System::ContainerStartTime] , GETDATE() ) + ",

    411,

    2,

    12,

    "+ @[System::MachineName] +"

    )

  • Just to add to that, you will need to escape the singal quote, with a single quote.

    e.g.

    declare @statement varchar(100)

    set @statement = 'select ''DAY NO.: '' + CAST(DATEPART(DAY,getdate()) AS VARCHAR(2))'

    execUTE( @statement)

    where you to write the actual sql it would be:

    select 'DAY NO.: ' + CAST(DATEPART(DAY,getdate()) AS VARCHAR(2))

    _____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

  • keep getting this error

    [Execute SQL Task] Error: Executing the query "INSERT INTO [dbo].[SSISLog]

    ([EventType..." failed with the following error: "Conversion failed when converting the varchar value '+ (DT_STR, 6, 1252)DATEDIFF("ss", @[System::StartTime] , GETDATE() ) + ' to data type int.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

  • ronan.healy (7/2/2013)


    hi

    thanks for reply it will be going into an ssis package

    so does it go in like this

    No. You need to wrap all non-numeric items in your INSERT statement in single quotes - something like this:

    ...

    VALUES

    (

    'OnPostExecute',

    '"+@[System::PackageName]+"',

    ...

    If you don't intend those double quotes to be part of what's inserted into the table, just leave them out.

    John

  • ya have it like that in my ssis package and get the same error

    [Execute SQL Task] Error: Executing the query "INSERT INTO [dbo].[SSISLog]

    ([EventType..." failed with the following error: "Conversion failed when converting the varchar value '"+ (DT_STR, 6, 1252)DATEDIFF("ss", @[System::StartTime] , GETDATE() ) + "' to data type int.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

  • I don't understand. If that's how it is in your package, what was it you posted earlier?

    You need the single quotes on all lines, even (especially) the ones with the offending "ss" characters in.

    John

  • hi

    ya still get error with the single quotes on all line

    INSERT INTO [dbo].[SSISLog]

    ([EventType]

    ,[PackageName]

    ,[TaskName]

    ,[EventCode]

    ,[EventDescription]

    ,[PackageDuration]

    ,[ContainerDuration]

    ,[InsertCount]

    ,[UpdateCount]

    ,[DeleteCount]

    ,[Host])

    VALUES

    (

    'OnPostExecute',

    '"+@[System::PackageName]+"',

    '"+@[System::TaskName]+"',

    0,

    '"+@[System::SourceDescription]+"',

    '"+ (DT_STR, 6, 1252)DATEDIFF("ss", @[System::StartTime] , GETDATE() ) + "',

    '"+ (DT_STR, 6, 1252)DATEDIFF("ss", @[System::ContainerStartTime] , GETDATE() ) + "',

    411,

    2,

    12,

    '"+ @[System::MachineName] +"'

    )

  • Mmmm... what happens if you paste that code into SSIS and run it there? I wonder whether it's anything to do with SET QUOTED_IDENTIFIER?

    John

  • get the same error in ssis package

  • Sorry, meant SSMS.

    John

  • no error at all in sql server

  • OK, if your're running exactly the same code - and you may want to check it's exactly the same - then it's got to be environmental. Try putting SET QUOTED_IDENTIFIER ON at the top in SSMS and see whether you get an error, or try putting SET QUOTED_IDENTIFIER OFF at the top of your code in SSIS and see whether that stops the error.

    John

  • get an error in sql server when i put that in.

    also still have same error in ssis dont no whats going on so

Viewing 15 posts - 1 through 15 (of 22 total)

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