Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

SSIS: Custom Logging Using Event Handlers Expand / Collapse
Author
Message
Posted Tuesday, July 2, 2013 7:30 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 8:02 AM
Points: 77, Visits: 144
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
Post #1469491
Posted Tuesday, July 2, 2013 7:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 5,233, Visits: 9,476
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
Post #1469504
Posted Tuesday, July 2, 2013 7:54 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 8:02 AM
Points: 77, Visits: 144
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] +"
)

Post #1469515
Posted Tuesday, July 2, 2013 8:07 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 5:38 AM
Points: 405, Visits: 1,136
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:

[code="plain"][/code]select 'DAY NO.: ' + CAST(DATEPART(DAY,getdate()) AS VARCHAR(2))


_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
Post #1469526
Posted Tuesday, July 2, 2013 8:13 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 8:02 AM
Points: 77, Visits: 144
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.
Post #1469536
Posted Tuesday, July 2, 2013 8:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 5,233, Visits: 9,476
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
Post #1469543
Posted Tuesday, July 2, 2013 8:23 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 8:02 AM
Points: 77, Visits: 144
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.
Post #1469548
Posted Tuesday, July 2, 2013 8:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 5,233, Visits: 9,476
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
Post #1469561
Posted Tuesday, July 2, 2013 8:39 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 8:02 AM
Points: 77, Visits: 144
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] +"'
)

Post #1469571
Posted Tuesday, July 2, 2013 9:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 5,233, Visits: 9,476
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
Post #1469589
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse