July 2, 2013 at 9:59 am
ok got it to work
but when i open up table in database it comes in like this
EventIDEventTypePackageNameTaskNameEventCodeEventDescriptionPackageDurationContainerDurationInsertCountUpdateCountDeleteCountHost
5OnPostExecute"+@[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() ) + "411212"+ @[System::MachineName] +"
which is just basically the sql code i have how do i get the fileds in db to fill in probably
July 2, 2013 at 10:07 am
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)
July 3, 2013 at 3:49 am
if i use variables how do i get the source description to pull back the error the way the ssis package produces it
July 3, 2013 at 8:45 am
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)
July 3, 2013 at 9:31 am
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
July 4, 2013 at 3:44 am
anyone any ideas why this isnt working right
July 11, 2013 at 7:20 am
I'll try and create an example for you
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
July 12, 2013 at 9:07 am
ronan, Here is a demo package i put together - I've put some annotation notes inside the package.
It's all very basic, but will show you what you need to do.
(this is an example for on error, but you'd do something very similar if you wanted to do some custom loggin on something OnProgress or OnWarning).
Hope that helps.:-D
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply