Sample Scripts
|
All samples use the OLE Automation Return Codes and Error Information
stored procedures listed in SQL Server Books Online.
|
msoleerror.sql
|
|
Simple stored procedure to execute a package.
|
spExecutePKG.sql
|
|
Stored procedure to execute a package, with additional parameters to set up to two global variables prior to execution.
|
spExecutePKGGlobalVariables.sql
|
|
Supporting stored procedure used to check step ExecutionResult property. (Required by spExecutePKG and spExecutePKGGlobalVariables).
|
spDisplayPKGErrors.sql
|
Script Usage
I generally create the OLE Automation Return Codes and Error Information stored
procedures in master, and keep the others in my utility type database in an effort
to keep the master database as clean as possible. The calling parameters are the same
for spExecutePKG and spExecutePKGGlobalVariables are the same, apart from the additional global variables.
Simple package execution, using integrated security:
EXEC spExecutePKGGlobalVariables @Server='MyServer',
@PkgName='MyPackage', @IntSecurity=1
|
Package execution with a package password and SQL Server security:
N.B. The SQL login comes from the current connection via SUSER_NAME()
EXEC spExecutePKGGlobalVariables @Server='MyServer',
@PkgName='MyPackage', @ServerPWD='xxxx', @PkgPWD='xxxx'
|
Set one global variable, and execute package. Results are returned in a temporary table for further analysis:
IF OBJECT_ID('tempdb..#PkgResults') IS NOT NULL
DROP TABLE #PkgResults
CREATE TABLE #PkgResults
(
StepName varchar(255) NOT NULL,
StepDescription varchar(255) NOT NULL,
Result bit NOT NULL
)
INSERT #PkgResults
EXEC spExecutePKGGlobalVariables @Server='MyServer',
@PkgName='MyPackage', @IntSecurity=1,
@GlobalV_1='VariableName=VariableValue'
SELECT StepName, StepDescription, Result FROM #PkgResults
|
StepName StepDescription Result
------------------------------ ------------------------ ------
DTSStep_DTSActiveScriptTask_1 ActX: Set Filename 0
DTSStep_DTSExecuteSQLTask_1 ExecSQL: Create Table 0
DTSStep_DTSDataPumpTask_1 Data Pump Task: Import 0
|
Credits
These stored procedures are variations of some written by Bill Hoghead,
and distributed via Neil Pike's SQL Server FAQ.
» 
IE 7
Firefox 2.0
Comment and Contribute
