USE compliance GO

IF OBJECT_ID('AutoDTS_complianceDumps') IS NOT NULL BEGIN DROP PROCEDURE AutoDTS_complianceDumps IF OBJECT_ID('AutoDTS_complianceDumps') IS NOT NULL PRINT '<<< FAILED DROPPING PROCEDURE AutoDTS_complianceDumps >>>' ELSE PRINT '<<< DROPPED PROCEDURE AutoDTS_complianceDumps >>>' END GO

CREATE PROCEDURE AutoDTS_complianceDumps ( @serverName nvarchar(255) = 'BWilhiteBofA' , @groupName nvarchar(255) = '[ALL]' , @path nvarchar(255) = 'c:\\Inetpub\\wwwroot\\dev_Compliance_Support\\data\\FileDump_Compliance\\' )

AS

-- Description: Automation of the DTS Package complianceDumps via tSQL. /* Note that if SQL Server is not run by a domain-level user (e.g. the SYSTEM account) then do not use UNC paths for the @path parameter. This code can be translated into an ActiveX Script and run as a SQL Server Job if need be:

Dim objDTSPack

Set objDTSPack = CreateObject("DTS.Package")

'Load Package using NT security.
Call objDTSPack.LoadFromSQLServer("BWilhiteBofA", , , 256, , , , "complianceDumps")

'Set Global Variables.
objDTSPack.GlobalVariables("g_strGroupName").Value = "[ALL]"
objDTSPack.GlobalVariables("g_strCSVActivityPath").Value = "\\\\bwilhitebofa\\FileDump_Compliance\\ActivityReport.csv"
objDTSPack.GlobalVariables("g_strCSVExceptionPath").Value = "\\\\bwilhitebofa\\FileDump_Compliance\\ExceptionReport.csv"
'Note that we are using UNC's for the g_strCSV[...] global variables
'as a domain-level user is running SQL Server Agent.

'Begin.
objDTSPack.FailOnError = True
objDTSPack.WriteCompletionStatusToNTEventLog = True

objDTSPack.Execute

'Clean up.
Set objDTSPack = Nothing

References:
DTS Packages & OLE Stored Procedures
http://www.swynk.com/friends/green/dtsole.asp

*/

-- Developer: Bryan D. Wilhite

SET NOCOUNT ON

DECLARE @token int , @hresult int , @context int , @progID nvarchar(255) , @errSrc nvarchar(255) , @errDescription nvarchar(255) , @isObject bit , @methodCall nvarchar(512)

, @DTSSQLStgFlag_UseTrustedConnection nvarchar(4) , @CSVActivityPath nvarchar(512) , @CSVExceptionPath nvarchar(512)

SET @DTSSQLStgFlag_UseTrustedConnection = '256' SET @CSVActivityPath = @path + 'ActivityReport.csv' SET @CSVExceptionPath = @path + 'ExceptionReport.csv'

SET @context = 1 /* 1 = In-process (.dll) OLE server only 4 = Local (.exe) OLE server only 5 = Both in-process and local OLE server allowed

Note: DTS runs in process with SQL Server so its creation context must be 1.

*/

SET @isObject = 0 SET @progID = 'DTS.Package'

-- Create DTS object. EXECUTE @hresult = sp_OACreate @progID=@progID, @objecttoken=@token OUTPUT, @context=@context IF @hresult <> 0 BEGIN EXECUTE sp_OAGetErrorInfo @objecttoken=@token, @source=@errSrc, @description=@errDescription OUTPUT SELECT PROGID = @progID , HRESULT = @hresult , SOURCE = @errSrc , [Description] = @errDescription END ELSE BEGIN SET @isObject = 1 END

-- Load Package using NT security. SET @methodCall = 'LoadFromSQLServer(ServerName:="' + @serverName + '", PackageName:="complianceDumps", Flags:=' + @DTSSQLStgFlag_UseTrustedConnection + ')' EXECUTE @hresult = sp_OAMethod @objecttoken=@token, @methodname=@methodCall IF @hresult <> 0 BEGIN EXECUTE sp_OAGetErrorInfo @objecttoken=@token, @source=@errSrc, @description=@errDescription OUTPUT SELECT PROGID = @progID , HRESULT = @hresult , SOURCE = @errSrc , [Description] = @errDescription END

-- Set Package Properties. EXECUTE @hresult = sp_OASetProperty @objecttoken=@token, @propertyname='FailOnError', @newvalue=TRUE IF @hresult <> 0 BEGIN EXECUTE sp_OAGetErrorInfo @objecttoken=@token, @source=@errSrc, @description=@errDescription OUTPUT SELECT PROGID = @progID , HRESULT = @hresult , SOURCE = @errSrc , [Description] = @errDescription END

EXECUTE @hresult = sp_OASetProperty @objecttoken=@token, @propertyname='WriteCompletionStatusToNTEventLog', @newvalue=TRUE IF @hresult <> 0 BEGIN EXECUTE sp_OAGetErrorInfo @objecttoken=@token, @source=@errSrc, @description=@errDescription OUTPUT SELECT PROGID = @progID , HRESULT = @hresult , SOURCE = @errSrc , [Description] = @errDescription END

-- Set Global Variables. EXECUTE @hresult = sp_OASetProperty @objecttoken=@token, @propertyname='GlobalVariables("g_strGroupName").Value', @newvalue=@groupName IF @hresult <> 0 BEGIN EXECUTE sp_OAGetErrorInfo @objecttoken=@token, @source=@errSrc, @description=@errDescription OUTPUT SELECT PROGID = @progID , HRESULT = @hresult , SOURCE = @errSrc , [Description] = @errDescription END

EXECUTE @hresult = sp_OASetProperty @objecttoken=@token, @propertyname='GlobalVariables("g_strCSVActivityPath").Value', @newvalue=@CSVActivityPath IF @hresult <> 0 BEGIN EXECUTE sp_OAGetErrorInfo @objecttoken=@token, @source=@errSrc, @description=@errDescription OUTPUT SELECT PROGID = @progID , HRESULT = @hresult , SOURCE = @errSrc , [Description] = @errDescription END

EXECUTE @hresult = sp_OASetProperty @objecttoken=@token, @propertyname='GlobalVariables("g_strCSVExceptionPath").Value', @newvalue=@CSVExceptionPath IF @hresult <> 0 BEGIN EXECUTE sp_OAGetErrorInfo @objecttoken=@token, @source=@errSrc, @description=@errDescription OUTPUT SELECT PROGID = @progID , HRESULT = @hresult , SOURCE = @errSrc , [Description] = @errDescription END

-- Execute. EXECUTE @hresult = sp_OAMethod @objecttoken=@token, @methodname='Execute' IF @hresult <> 0 BEGIN EXECUTE sp_OAGetErrorInfo @objecttoken=@token, @source=@errSrc, @description=@errDescription OUTPUT SELECT PROGID = @progID , HRESULT = @hresult , SOURCE = @errSrc , [Description] = @errDescription END

-- Clean up. IF @isObject = 1 BEGIN EXECUTE @hresult = sp_OADestroy @objecttoken=@token IF @hresult <> 0 BEGIN EXECUTE sp_OAGetErrorInfo @objecttoken=@token, @source=@errSrc, @description=@errDescription OUTPUT SELECT PROGID = @progID , HRESULT = @hresult , SOURCE = @errSrc , [Description] = @errDescription END END

GO

GRANT EXECUTE ON AutoDTS_complianceDumps TO dbWebSolutionsUsers GO

IF OBJECT_ID('AutoDTS_complianceDumps') IS NOT NULL PRINT '<<< CREATED PROCEDURE AutoDTS_complianceDumps >>>' ELSE PRINT '<<< FAILED CREATING PROCEDURE AutoDTS_complianceDumps >>>' GO