July 2, 2013 at 7:30 am
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
July 2, 2013 at 7:43 am
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
July 2, 2013 at 7:54 am
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] +"
)
July 2, 2013 at 8:07 am
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)
July 2, 2013 at 8:13 am
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.
July 2, 2013 at 8:19 am
ronan.healy (7/2/2013)
hithanks 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
July 2, 2013 at 8:23 am
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.
July 2, 2013 at 8:31 am
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
July 2, 2013 at 8:39 am
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] +"'
)
July 2, 2013 at 9:06 am
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
July 2, 2013 at 9:12 am
get the same error in ssis package
July 2, 2013 at 9:14 am
Sorry, meant SSMS.
John
July 2, 2013 at 9:20 am
no error at all in sql server
July 2, 2013 at 9:24 am
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
July 2, 2013 at 9:27 am
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