SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS: Custom Logging Using Event Handlers


SSIS: Custom Logging Using Event Handlers

Author
Message
ronan.healy
ronan.healy
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 232
get the same error in ssis package
John Mitchell-245523
John Mitchell-245523
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14163 Visits: 15963
Sorry, meant SSMS.

John
ronan.healy
ronan.healy
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 232
no error at all in sql server
John Mitchell-245523
John Mitchell-245523
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14163 Visits: 15963
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
ronan.healy
ronan.healy
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 232
get an error in sql server when i put that in.
also still have same error in ssis dont no whats going on so
ronan.healy
ronan.healy
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 232
ok got it to work

but when i open up table in database it comes in like this

EventID EventType PackageName TaskName EventCode EventDescription PackageDuration ContainerDuration InsertCount UpdateCount DeleteCount Host
5 OnPostExecute "+@[System:TongueackageName]+" "+@[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] +"



which is just basically the sql code i have how do i get the fileds in db to fill in probably
dave-dj
dave-dj
SSC Eights!
SSC Eights! (990 reputation)SSC Eights! (990 reputation)SSC Eights! (990 reputation)SSC Eights! (990 reputation)SSC Eights! (990 reputation)SSC Eights! (990 reputation)SSC Eights! (990 reputation)SSC Eights! (990 reputation)

Group: General Forum Members
Points: 990 Visits: 1149
Hi ronan

I can see you problem now and probably why it's not been. Lear to others.

Your using a SQL task but have put in ssis expressions. They are very different things so you can't mix them as your code is trying to do.

To point you in the right direction you can do this one of two way:

1st option: Declare a package variable and use an SSIS Expression to generate the SQL statement and the have a second step to execute that sql statement, by using a SQL task where the source type is your variable.

2nd option: The second option is first create two package variables and set them to evaluate as expression for these two lines

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

.... Without all the double quotes etc.

Then use a SQL task either your SQL statement, replacing the package variables with a ?

I.e.

VALUES
(
,?
,?
,?
) etc etc.

Then use the parameters table to map the package parameters in order ( these will replace the ?)

Hope that makes sense

_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
ronan.healy
ronan.healy
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 232
if i use variables how do i get the source description to pull back the error the way the ssis package produces it
dave-dj
dave-dj
SSC Eights!
SSC Eights! (990 reputation)SSC Eights! (990 reputation)SSC Eights! (990 reputation)SSC Eights! (990 reputation)SSC Eights! (990 reputation)SSC Eights! (990 reputation)SSC Eights! (990 reputation)SSC Eights! (990 reputation)

Group: General Forum Members
Points: 990 Visits: 1149
You would probably need to have the SQL task logging the error as an OnError event handler task at package level (or a suitable executable below that).

_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
ronan.healy
ronan.healy
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 232
i have a script this is it

Public Sub Main()
Dim S As String

'Building a string that proof that the values of the variables
' is originated from the event fires in the child package

S = "TaskName: " & Dts.Variables("System::TaskName") _
.Value.ToString() & vbCrLf _
& "SourceName: " & Dts.Variables("System::SourceName") _
.Value.ToString() & vbCrLf _
& "SourceDescription: " & Dts.Variables("System::SourceDescription") _
.Value.ToString() & vbCrLf _
& "MachineName: " & Dts.Variables("System::MachineName") _
.Value.ToString() & vbCrLf _
& "PackageName: " & Dts.Variables("System:TongueackageName") _
.Value.ToString()

'Showing the string value as a message box
MsgBox(S)

Dts.TaskResult = ScriptResults.Success
End Sub



seem to be right when the message box pops up pops up the right descriptions.

this script runs into my sql so it insert it into the table but its still just adding in the @+Task name etc
here is my script

INSERT INTO [dbo].[SSISLog]
([EventType]
,[PackageName]
,[TaskName]
,[EventCode]
,[EventDescription]
,[Host])

VALUES
(
'OnPostExecute',
'"+@[System:TongueackageName]+"',
'"+@[System::TaskName]+"',
0,
'"+@[System::SourceDescription]+"',
'"+ @[System::MachineName] +"'
)



what am i doing wrong it wont populate the table right
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search