Database Reference
In-Depth Information
If Exists(Select s.name + '.' + p.name
From sys.procedures p
Join sys.schemas s
On s.schema_id = p.schema_id
Where s.name = 'log'
And p.name = 'LogApplicationFailure')
begin
print 'Dropping log.LogApplicationFailure stored
procedure'
Drop Procedure [log].LogApplicationFailure
print 'Log.LogApplicationFailure stored procedure
dropped'
end
print 'Creating log.LogApplicationFailure stored
procedure'
go
Create Procedure [log].LogApplicationFailure
@AppInstanceID int
As
update log.SSISAppInstance
set EndDateTime = GetDate()
, Status = 'Failed'
where AppInstanceID = @AppInstanceID
go
print 'Log.LogApplicationFailure stored procedure
created.'
print ''
Now let's return to SSDT and add application instance logging to the
Par-
ent.dtsx
package. Drag a new Execute SQL task to the control flow and rename it
Log Start of Application
. Set the ResultSet property to Single Row. Set the Connec-
tionType property to ADO.Net and Connection to the
SSISConfig
connection man-
ager. Set the SQLStatement property to
log.LogStartOfApplication
and the
IsQueryStoredProcedure property to
True
. Navigate to the Parameter Mapping page
and add a new parameter: mapping the
User::ApplicationName
SSIS variable
to the
ApplicationName
parameter for the
log.LogStartOfApplication
Search WWH ::
Custom Search