DECLARE @hr int
DECLARE @output varchar(255)
DECLARE @source varchar(255)
DECLARE @description varchar(255)

PRINT 'OLE Automation Error Information'

EXEC @hr = sp_OAGetErrorInfo @object, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END

GO

SET NOCOUNT ON

DECLARE @StepCount int
DECLARE @Steps int
DECLARE @Step int
DECLARE @StepResult int
DECLARE @oPkgResult int
DECLARE @hr int

DECLARE @StepName varchar(255)
DECLARE @StepDescription varchar(255)

IF OBJECT_ID('tempdb..#PkgResult') IS NOT NULL
DROP TABLE #PkgResult

CREATE TABLE #PkgResult
(
StepName varchar(255) NOT NULL,
StepDescription varchar(255) NOT NULL,
Result bit NOT NULL
)

SELECT @oPkgResult = 0

EXEC @hr = sp_OAGetProperty @oPkg, 'Steps', @Steps OUTPUT
IF @hr <> 0
BEGIN
PRINT '*** Unable to get steps'
EXEC sp_displayoaerrorinfo @oPkg , @hr
RETURN 1
END

EXEC @hr = sp_OAGetProperty @Steps, 'Count', @StepCount OUTPUT
IF @hr <> 0
BEGIN
PRINT '*** Unable to get number of steps'
EXEC sp_displayoaerrorinfo @Steps , @hr
RETURN 1
END

WHILE @StepCount > 0
BEGIN
EXEC @hr = sp_OAGetProperty @Steps, 'Item', @Step OUTPUT, @StepCount
IF @hr <> 0
BEGIN
PRINT '*** Unable to get step'
EXEC sp_displayoaerrorinfo @Steps , @hr
RETURN 1
END

EXEC @hr = sp_OAGetProperty @Step, 'ExecutionResult', @StepResult OUTPUT
IF @hr <> 0
BEGIN
PRINT '*** Unable to get ExecutionResult'
EXEC sp_displayoaerrorinfo @Step , @hr
RETURN 1
END

EXEC @hr = sp_OAGetProperty @Step, 'Name', @StepName OUTPUT
IF @hr <> 0
BEGIN
PRINT '*** Unable to get step Name'
EXEC sp_displayoaerrorinfo @Step , @hr
RETURN 1
END

EXEC @hr = sp_OAGetProperty @Step, 'Description', @StepDescription OUTPUT
IF @hr <> 0
BEGIN
PRINT '*** Unable to get step Description'
EXEC sp_displayoaerrorinfo @Step , @hr
RETURN 1
END

INSERT #PkgResult VALUES(@StepName, @StepDescription, @StepResult)
PRINT 'Step ' + @StepName + ' (' + @StepDescription + ') ' + CASE WHEN @StepResult = 0 THEN 'Succeeded' ELSE 'Failed' END

SELECT @StepCount = @StepCount - 1
SELECT @oPkgResult = @oPkgResult + @StepResult
END

SELECT * FROM #PkgResult

IF @oPkgResult > 0
BEGIN
PRINT 'Package had ' + CAST(@oPkgResult as varchar) + ' failed step(s)'
RETURN 9
END
ELSE
BEGIN
PRINT 'Packge Succeeded'
RETURN 0
END

GO

SET NOCOUNT ON
/*
Return Values
- 0 Successfull execution of Package
- 1 OLE Error
- 9 Failure of Package
*/
DECLARE @hr int, @ret int, @oPKG int, @Cmd varchar(1000)

-- Create a Pkg Object
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUTPUT
IF @hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN 1
END

-- Evaluate Security and Build LoadFromSQLServer Statement
IF @IntSecurity = 0
SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "' + SUSER_SNAME() + '", "' + @ServerPWD + '", 0, "' + @PkgPWD + '", , , "' + @PkgName + '")'
ELSE
SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "", "", 256, "' + @PkgPWD + '", , , "' + @PkgName + '")'

EXEC @hr = sp_OAMethod @oPKG, @Cmd, NULL

IF @hr <> 0
BEGIN
PRINT '*** LoadFromSQLServer failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END

-- Execute Pkg
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr <> 0
BEGIN
PRINT '*** Execute failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END

-- Check Pkg Errors
EXEC @ret=spDisplayPkgErrors @oPKG

-- Unitialize the Pkg
EXEC @hr = sp_OAMethod @oPKG, 'UnInitialize'
IF @hr <> 0
BEGIN
PRINT '*** UnInitialize failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END

-- Clean Up
EXEC @hr = sp_OADestroy @oPKG
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END

RETURN @ret

GO

AS

CREATE TABLE #tempTracking(
[date] datetime,
visitorCount int,
browser varchar(50)
)

INSERT INTO #tempTracking
SELECT [date],COUNT(browser) as visitorCount,browser
FROM tracking
group by [date],browser

SELECT SUM(visitorCount) as visitorSum, browser
FROM #tempTracking
GROUP BY browser
ORDER BY browser

DROP TABLE #tempTracking

GO