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 9:12 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 27, 2014 6:30 AM
Points: 71, Visits: 132
get the same error in ssis package
Post #1469595
Posted Tuesday, July 2, 2013 9:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:23 AM
Points: 5,230, Visits: 9,456
Sorry, meant SSMS.

John
Post #1469596
Posted Tuesday, July 2, 2013 9:20 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 27, 2014 6:30 AM
Points: 71, Visits: 132
no error at all in sql server
Post #1469603
Posted Tuesday, July 2, 2013 9:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:23 AM
Points: 5,230, Visits: 9,456
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
Post #1469609
Posted Tuesday, July 2, 2013 9:27 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 27, 2014 6:30 AM
Points: 71, Visits: 132
get an error in sql server when i put that in.
also still have same error in ssis dont no whats going on so
Post #1469611
Posted Tuesday, July 2, 2013 9:59 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 27, 2014 6:30 AM
Points: 71, Visits: 132
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::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] +"

which is just basically the sql code i have how do i get the fileds in db to fill in probably
Post #1469640
Posted Tuesday, July 2, 2013 10:07 AM


SSC-Addicted

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

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 9:39 AM
Points: 405, Visits: 1,135
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)
Post #1469646
Posted Wednesday, July 3, 2013 3:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 27, 2014 6:30 AM
Points: 71, Visits: 132
if i use variables how do i get the source description to pull back the error the way the ssis package produces it
Post #1469958
Posted Wednesday, July 3, 2013 8:45 AM


SSC-Addicted

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

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 9:39 AM
Points: 405, Visits: 1,135
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)
Post #1470092
Posted Wednesday, July 3, 2013 9:31 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 27, 2014 6:30 AM
Points: 71, Visits: 132
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::PackageName") _
.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::PackageName]+"',
'"+@[System::TaskName]+"',
0,
'"+@[System::SourceDescription]+"',
'"+ @[System::MachineName] +"'
)

what am i doing wrong it wont populate the table right
Post #1470129
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse