April 11, 2002 at 4:13 am
Hello everyone,
Can anyone help me on this problem:
I have a VB program (.exe file) located on a computer A, and the DTS is on SQL server 2000 on computer B. My VB program is trying to call this DTS, but it doesn't work. However I have a computer C which have SQL client tools installed, the VB program works well on this computer C.
Anyone can tell me if there is a solution to solve my problem other than install SQL client tools in computer A?? Thanks a lot.
Tina
April 11, 2002 at 6:50 am
From what I understand the call to dts will invoke the OLE Object in the address space of the calling client i.e. computer a so if that's the way you've got to go then yes you have to install all the gubbins on computer a.
HOWEVER there are a couple of options you could try. If your application is using ASP then one route would be to pass the call through to a COM object (activex) residing on the server this should then run the dts in the server's address space not computer a. (This is achieved by serverside scripting within the <% %> tags.
The other might be to call a stored procedure in your data base which inturn calls DTS either through xp_cmdshell or preferably using the DTS OLE object. I've not tried that one but again I suspect that DTS will run in the server's address space rather than computer a
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
April 15, 2002 at 4:18 am
Hi crosspath,
Thanks for the answer. here is my code:
------------------------------
Set objPackage = New DTS.Package
Set mobjPkgEvents = objPackage
objPackage.FailOnError = True
Set objStep = objPackage.Steps.New
Set objTask = objPackage.Tasks.New("DTSExecutePackageTask")
Set objExecpkg = objTask.CustomTask
objPackage.LoadFromSQLServer "SQL2000servername", "userid", "password", , , , , "MY_DTS"
objPackage.Execute
---------------------------
Without installing SQL client, I got the following error:
---------------------------------------
Run-time error '429'
ActiveX component can't create object.
---------------------------------------
So any suggestion what should I do? Thanks.
Tina
April 15, 2002 at 5:03 am
Hidee Ho
I'm in the same boat a tinawx (sorta). Our develepers want to be able to run a DTS package from a client machine but I'm not keen to install the client tools. I'm not a developer so an idiots guide to Tinawx's answer would be greatly appreciated.
Delta Kilo
April 15, 2002 at 5:08 am
Right, this is because the DTS activex com objects do not exist on the computer A in question. You will have to install them which I am not sure if you can just drop in the DTS com objects or you are required to install client tools. See http://support.microsoft.com/default.aspx?scid=kb;en-us;Q228940 concerning redistributable DTS files. The files you most liekly will need to redistribute are
custtask.dll
dtspump.dll
dtspkg.dll
at minimum.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 15, 2002 at 7:30 am
Hi tinawx. OK on the basis that a doctor should be prepared to try their own medicine I've tried this out on my client's extremely ancient and unstable Win98 machine connecting to SQL server 7 on a creaking NT network.
First create a utility sp thus
PRINT 'STORED PROCEDURE : dbo.prc_util_execute_dtspackage'
GO
IF EXISTS (SELECT 1 from dbo.sysobjects
WHERE id = Object_id('dbo.prc_util_execute_dtspackage') AND (type = 'P' or type = 'RF'))
BEGIN
DROP PROC dbo.prc_util_execute_dtspackage
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
/*******************************************************************************
Now For The Procedure Proper
*******************************************************************************/
CREATE PROCEDURE prc_util_execute_dtspackage
@ServerName sysname,
@ServerUserName sysname,
@ServerPassword sysname,
@PackageName sysname
AS
DECLARE
@ErrorValue INT,
@Object INT,
@ErrorDescription VARCHAR(255)
/*********************************
Create A Package Object
*********************************/
EXEC @ErrorValue = sp_OACreate 'DTS.PACKAGE', @Object OUTPUT
IF @ErrorValue <> 0 BEGIN
EXEC sp_OAGetErrorInfo @Object, NULL, @ErrorDescription OUT
SELECT @ErrorDescription AS ErrorDescription
RETURN
END
/********************************
Load The Method With Required Params
********************************/
EXEC @ErrorValue = sp_OAMethod @Object,
'LoadFromSqlServer',
NULL,
@ServerName = @ServerName,
@ServerUserName = @ServerUserName,
@PackageName = @PackageName,
@Flags = 0,
@PackagePassword = '',
@ServerPassword = @ServerPassword
IF @ErrorValue <> 0 BEGIN
EXEC sp_OAGetErrorInfo @Object, NULL, @ErrorDescription OUT
SELECT @ErrorDescription AS ErrorDescription
RETURN
END
/**********************************
Execute The Method
**********************************/
EXEC @ErrorValue = sp_OAMethod @Object, 'Execute'
IF @ErrorValue <> 0 BEGIN
EXEC sp_OAGetErrorInfo @Object, NULL, @ErrorDescription OUT
SELECT @ErrorDescription AS ErrorDescription
RETURN
END
/*********************************
Empty The Package Object
*********************************/
EXEC @ErrorValue = sp_OAMethod @Object, 'UnInitialize'
/********************************
Destroy the Object
********************************/
EXEC @ErrorValue = sp_OADestroy @Object
RETURN
this basically allow you to poass in the required parameters and execute your desired DTS package using a simple EXEC. I then called this from my client machine which definetely has not even heard of the required DLL's and ........ It ran perfectly. I guess DTS having been invoked on the server through the SP runs in the Server's address space where it finds everything a hungry little package could ever want!
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
April 16, 2002 at 12:27 am
Hi crosspatch,
Yes! I got it. Thank you very much.
Tina
May 8, 2002 at 8:01 am
I have the same problem. But I think I can't use the suggested solution because I have to set global parameters in the dts package.
Can I do this from a SP?
Nico
May 8, 2002 at 8:11 am
Hey,
Had the same issue yesterday with the ActiveX object not being created. Turned out I missed a couple of DLL's that the dtspkg.dll file needed to register itself.
To find these you can use the dependancy walker (search for depends.exe). Load up dtspkg.dll and it will tell you all the associated files it requires to be loaded!
Clive
Clive Strong
May 8, 2002 at 8:15 am
Good solve - depends is a great tool. Other handy ones I use are RegMon and FileMon, lets me see what is going on!
Andy
May 9, 2002 at 4:38 am
Hi Tina
I have developed a dts package application
for sql server data convert to Text,Excel and MDB File.
Private Sub DTSTask(ByVal FileType As String, ByVal ServerName As String, ByVal DataBaseName As String, ByVal UserId As String, ByVal Password, ByVal destination_file_full_path As String, ByVal colnames As String, ByVal colcount As Integer, ByVal SourceSql As String, ByVal destination_table_name As String)
'************* Start Variable Declaration ********************
Dim oCustomTask1 As DTS.ExecuteSQLTask
Dim oCustomTask2 As DTS.DataPumpTask
Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup
Dim oStep As DTS.Step
Dim oTransform As DTS.Transformation
Dim oPrecConstraint As DTS.PrecedenceConstraint
'*************End Variable Declaration ********************
'********* Start Description of DTS Package *********************
Set goPackage = New DTS.Package
goPackage.Name = "New Package"
goPackage.Description = "DTS package description"
goPackage.WriteCompletionStatusToNTEventLog = False
goPackage.FailOnError = False
goPackage.PackagePriorityClass = 2
goPackage.MaxConcurrentSteps = 4
goPackage.LineageOptions = 0
goPackage.UseTransaction = True
goPackage.TransactionIsolationLevel = 4096
goPackage.AutoCommitTransaction = True
goPackage.RepositoryMetadataOptions = 0
goPackage.UseOLEDBServiceComponents = True
'********* End Description of DTS Package *********************
'************* Start Connection Variable Declaration ********************
Dim oConnProperty As DTS.OleDBProperty
Dim oConnection As DTS.Connection
'********** Connection with SQL Server *************************
Set oConnection = goPackage.Connections.New("SQLOLEDB")
oConnection.ConnectionProperties("Persist Security Info") = True
oConnection.ConnectionProperties("User ID") = UserId
oConnection.ConnectionProperties("Initial Catalog") = DataBaseName
oConnection.ConnectionProperties("Data Source") = ServerName
oConnection.ConnectionProperties("Application Name") = "DTS Import/Export Wizard"
oConnection.Name = "Connection 1"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = ServerName
oConnection.UserId = UserId
oConnection.ConnectionTimeout = 60
oConnection.Catalog = DataBaseName
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False
oConnection.Password = Password
goPackage.Connections.Add oConnection
Set oConnection = Nothing
'**********************************************************************
If UCase(Trim(FileType)) = "TXT" Then
' Destination Connection with Text File
Set oConnection = goPackage.Connections.New("DTSFlatFile")
oConnection.ConnectionProperties("Data Source") = destination_file_full_path
oConnection.ConnectionProperties("Mode") = 3
oConnection.ConnectionProperties("Row Delimiter") = vbCrLf
oConnection.ConnectionProperties("File Format") = 1
oConnection.ConnectionProperties("Column Delimiter") = ","
oConnection.ConnectionProperties("File Type") = 1
oConnection.ConnectionProperties("Skip Rows") = 0
'oConnection.ConnectionProperties("Text Qualifier") = """"
oConnection.ConnectionProperties("First Row Column Name") = True
oConnection.ConnectionProperties("Column Names") = colnames
oConnection.ConnectionProperties("Number of Column") = colcount
oConnection.ConnectionProperties("Text Qualifier Col Mask: 0=no, 1=yes, e.g. 0101") = "111111110"
oConnection.ConnectionProperties("Blob Col Mask: 0=no, 1=yes, e.g. 0101") = "000000000"
oConnection.Name = "Connection 2"
oConnection.ID = 2
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = destination_file_full_path
oConnection.ConnectionTimeout = 60
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False
goPackage.Connections.Add oConnection
Set oConnection = Nothing
'---------------------------------------------------------------------------
' create package steps information
'---------------------------------------------------------------------------
'------------- a new step defined below
'------------- a new step defined below
Set oStep = goPackage.Steps.New
oStep.Name = "Copy Data from authors to C:\Documents and Settings\Administrator\Desktop\test.txt Step"
oStep.Description = "Copy Data from authors to C:\Documents and Settings\Administrator\Desktop\test.txt Step"
oStep.ExecutionStatus = 1
oStep.TaskName = "Copied data in table C:\Documents and Settings\Administrator\Desktop\test.txt"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
'oStep.FailPackageOnError = False
goPackage.Steps.Add oStep
Set oStep = Nothing
Set oTask = goPackage.Tasks.New("DTSDataPumpTask")
Set oCustomTask2 = oTask.CustomTask
oCustomTask2.Name = "Copied data in table C:\Documents and Settings\Administrator\Desktop\test.txt"
oCustomTask2.Description = "Copied data in table C:\Documents and Settings\Administrator\Desktop\test.txt"
oCustomTask2.SourceConnectionID = 1
oCustomTask2.SourceSQLStatement = SourceSql
oCustomTask2.DestinationConnectionID = 2
oCustomTask2.DestinationObjectName = destination_file_full_path
oCustomTask2.ProgressRowCount = 1000
oCustomTask2.MaximumErrorCount = 0
oCustomTask2.FetchBufferSize = 1
oCustomTask2.UseFastLoad = True
oCustomTask2.InsertCommitSize = 0
oCustomTask2.ExceptionFileColumnDelimiter = "|"
oCustomTask2.ExceptionFileRowDelimiter = vbCrLf
oCustomTask2.AllowIdentityInserts = False
oCustomTask2.FirstRow = 0
oCustomTask2.LastRow = 0
oCustomTask2.FastLoadOptions = 2
'oCustomTask1.ExceptionFileOptions = 1
'oCustomTask1.DataPumpOptions = 0
Set oTransform = oCustomTask2.Transformations.New("DTS.DataPumpTransformCopy")
With oTransform
.Name = "Transform"
.TransformFlags = DTSTransformFlag_AllowLosslessConversion
End With
oCustomTask2.Transformations.Add oTransform
Set oTransform = Nothing
goPackage.Tasks.Add oTask
Set oCustomTask2 = Nothing
Set oTask = Nothing
ElseIf UCase(Trim(FileType)) = "MDB" Then
Set oConnection = goPackage.Connections.New("Microsoft.Jet.OLEDB.4.0")
oConnection.ConnectionProperties("Data Source") = destination_file_full_path
oConnection.ConnectionProperties("Mode") = 3
oConnection.Name = "Connection 2"
oConnection.ID = 2
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = destination_file_full_path
oConnection.ConnectionTimeout = 60
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False
'If you have a password for this connection, please uncomment and add your password below.
'oConnection.Password = "<put the password here>"
goPackage.Connections.Add oConnection
Set oConnection = Nothing
'---------------------------------------------------------------------------
' create package steps information
'---------------------------------------------------------------------------
'------------- a new step defined below
Set oStep = goPackage.Steps.New
oStep.Name = "Create Table authors Step"
oStep.Description = "Create Table authors Step"
oStep.ExecutionStatus = 1
oStep.TaskName = "Create Table authors Task"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
'oStep.FailPackageOnError = False
goPackage.Steps.Add oStep
Set oStep = Nothing
'------------- a new step defined below
Set oStep = goPackage.Steps.New
oStep.Name = "Copy Data from authors to authors Step"
oStep.Description = "Copy Data from authors to authors Step"
oStep.ExecutionStatus = 1
oStep.TaskName = "Copy Data from authors to authors Task"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = True
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
'oStep.FailPackageOnError = False
goPackage.Steps.Add oStep
Set oStep = Nothing
'------------- a precedence constraint for steps defined below
Set oStep = goPackage.Steps("Copy Data from authors to authors Step")
Set oPrecConstraint = oStep.PrecedenceConstraints.New("Create Table authors Step")
oPrecConstraint.StepName = "Create Table authors Step"
oPrecConstraint.PrecedenceBasis = 0
oPrecConstraint.Value = 4
oStep.PrecedenceConstraints.Add oPrecConstraint
Set oPrecConstraint = Nothing
Set oTask = goPackage.Tasks.New("DTSExecuteSQLTask")
Set oCustomTask1 = oTask.CustomTask
oCustomTask1.Name = "Create Table authors Task"
oCustomTask1.Description = "Create Table authors Task"
sql = "Create table `" & destination_table_name & "`("
arr = Split(colnames, ",")
For m_loopVar = 0 To UBound(arr)
sql = sql & " `" & arr(m_loopVar) & "` varchar Null, "
Next
sql = Left(sql, Len(sql) - 2)
sql = sql & " )"
oCustomTask1.SQLStatement = sql
oCustomTask1.ConnectionID = 2
oCustomTask1.CommandTimeout = 0
'oCustomTask1.OutputAsRecordset = False
goPackage.Tasks.Add oTask
Set oCustomTask1 = Nothing
Set oTask = Nothing
Set oTask = goPackage.Tasks.New("DTSDataPumpTask")
Set oCustomTask2 = oTask.CustomTask
oCustomTask2.Name = "Copy Data from authors to authors Task"
oCustomTask2.Description = "Copy Data from authors to authors Task"
oCustomTask2.SourceConnectionID = 1
oCustomTask2.SourceSQLStatement = SourceSql
oCustomTask2.DestinationConnectionID = 2
oCustomTask2.DestinationObjectName = destination_table_name
oCustomTask2.ProgressRowCount = 1000
oCustomTask2.MaximumErrorCount = 0
oCustomTask2.FetchBufferSize = 1
oCustomTask2.UseFastLoad = True
oCustomTask2.InsertCommitSize = 0
oCustomTask2.ExceptionFileColumnDelimiter = "|"
oCustomTask2.ExceptionFileRowDelimiter = vbCrLf
oCustomTask2.AllowIdentityInserts = False
oCustomTask2.FirstRow = 0
oCustomTask2.LastRow = 0
oCustomTask2.FastLoadOptions = 2
'oCustomTask2.ExceptionFileOptions = 1
'oCustomTask2.DataPumpOptions = 0
Set oTransform = oCustomTask2.Transformations.New("DTS.DataPumpTransformCopy")
With oTransform
.Name = "Transform"
.TransformFlags = DTSTransformFlag_AllowLosslessConversion
End With
oCustomTask2.Transformations.Add oTransform
Set oTransform = Nothing
goPackage.Tasks.Add oTask
Set oCustomTask2 = Nothing
Set oTask = Nothing
ElseIf UCase(Trim(FileType)) = "XLS" Then
Set oConnection = goPackage.Connections.New("Microsoft.Jet.OLEDB.4.0")
oConnection.ConnectionProperties("Data Source") = destination_file_full_path
oConnection.ConnectionProperties("Extended Properties") = "Excel 8.0;HDR=YES;"
oConnection.Name = "Connection 2"
oConnection.ID = 2
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = destination_file_full_path
oConnection.ConnectionTimeout = 60
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False
'If you have a password for this connection, please uncomment and add your password below.
'oConnection.Password = "<put the password here>"
goPackage.Connections.Add oConnection
Set oConnection = Nothing
'---------------------------------------------------------------------------
' create package steps information
'---------------------------------------------------------------------------
'------------- a new step defined below
Set oStep = goPackage.Steps.New
oStep.Name = "Create Table authors Step"
oStep.Description = "Create Table authors Step"
oStep.ExecutionStatus = 1
oStep.TaskName = "Create Table authors Task"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
'oStep.FailPackageOnError = False
goPackage.Steps.Add oStep
Set oStep = Nothing
'------------- a new step defined below
Set oStep = goPackage.Steps.New
oStep.Name = "Copy Data from authors to authors Step"
oStep.Description = "Copy Data from authors to authors Step"
oStep.ExecutionStatus = 1
oStep.TaskName = "Copy Data from authors to authors Task"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = True
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
'oStep.FailPackageOnError = False
goPackage.Steps.Add oStep
Set oStep = Nothing
'------------- a precedence constraint for steps defined below
Set oStep = goPackage.Steps("Copy Data from authors to authors Step")
Set oPrecConstraint = oStep.PrecedenceConstraints.New("Create Table authors Step")
oPrecConstraint.StepName = "Create Table authors Step"
oPrecConstraint.PrecedenceBasis = 0
oPrecConstraint.Value = 4
oStep.PrecedenceConstraints.Add oPrecConstraint
Set oPrecConstraint = Nothing
Set oTask = goPackage.Tasks.New("DTSExecuteSQLTask")
Set oCustomTask1 = oTask.CustomTask
oCustomTask1.Name = "Create Table authors Task"
oCustomTask1.Description = "Create Table authors Task"
sql = "Create table `" & destination_table_name & "`("
arr = Split(colnames, ",")
For m_loopVar = 0 To UBound(arr)
sql = sql & " `" & arr(m_loopVar) & "` varchar(240) Null, "
Next
sql = Left(sql, Len(sql) - 2)
sql = sql & " )"
oCustomTask1.SQLStatement = sql
oCustomTask1.ConnectionID = 2
oCustomTask1.CommandTimeout = 0
'oCustomTask1.OutputAsRecordset = False
goPackage.Tasks.Add oTask
Set oCustomTask1 = Nothing
Set oTask = Nothing
Set oTask = goPackage.Tasks.New("DTSDataPumpTask")
Set oCustomTask2 = oTask.CustomTask
oCustomTask2.Name = "Copy Data from authors to authors Task"
oCustomTask2.Description = "Copy Data from authors to authors Task"
oCustomTask2.SourceConnectionID = 1
oCustomTask2.SourceSQLStatement = SourceSql
oCustomTask2.DestinationConnectionID = 2
oCustomTask2.DestinationObjectName = destination_table_name
oCustomTask2.ProgressRowCount = 1000
oCustomTask2.MaximumErrorCount = 0
oCustomTask2.FetchBufferSize = 1
oCustomTask2.UseFastLoad = True
oCustomTask2.InsertCommitSize = 0
oCustomTask2.ExceptionFileColumnDelimiter = "|"
oCustomTask2.ExceptionFileRowDelimiter = vbCrLf
oCustomTask2.AllowIdentityInserts = False
oCustomTask2.FirstRow = 0
oCustomTask2.LastRow = 0
oCustomTask2.FastLoadOptions = 2
'oCustomTask2.ExceptionFileOptions = 1
'oCustomTask2.DataPumpOptions = 0
Set oTransform = oCustomTask2.Transformations.New("DTS.DataPumpTransformCopy")
With oTransform
.Name = "Transform"
.TransformFlags = DTSTransformFlag_AllowLosslessConversion
End With
oCustomTask2.Transformations.Add oTransform
Set oTransform = Nothing
goPackage.Tasks.Add oTask
Set oCustomTask2 = Nothing
Set oTask = Nothing
Else
Exit Sub
End If
goPackage.Execute
For m_loopVar = 1 To goPackage.Steps.Count
With goPackage.Steps(m_loopVar)
If .ExecutionStatus = DTSStepExecStat_Completed Then
If .ExecutionResult = DTSStepExecResult_Failure Then
'get step error info, update status bar
.GetExecutionErrorInfo lpErrorCode, sErrSource, sErrDescr
'MsgBox "Step '" & .Name & "' error: 0x" & Hex(lpErrorCode) & _
vbCrLf & sErrDescr & "No:-" & lpErrorCode, vbExclamation, goPackage.Description
Err.Raise lpErrorCode, Err.Source, sErrDescr
End If
End If
End With
Next m_loopVar
goPackage.UnInitialize
'to save a package instead of executing it, comment out the executing package line above and uncomment the saving package line
Set goPackage = Nothing
End Sub
May 9, 2002 at 10:24 pm
Nico Verbaenen
You can set any property of your object (eg DTS package) using sp_OASetProperty this includes also Global Variables
like:
SET @GVName = 'gvName'
SET @GVValue = someValue
--
SET @Cmd = 'GlobalVariables("' + @GVName + '").Value'
EXEC @hr = sp_OASetProperty @oPKG, @Cmd, @GVValue
Below is my routine which calls DTSPackage and I can set as many global variables I like - I pass it as one parameter separated by '~' and variablename and its value is separated by '='
'gv1=1~gv2=20020411~gv3=chrValue'
call like this:
EXEC @Ret=spExecutePKGAllGlobalVariables
@serverName,
@PackageName,
'uesrName',
'password',
0,
'',
@chrAssignChar,
@chrSearchFor,
@gvInfo
Cheers
Tom
here is the sp:
CREATE PROC spExecutePKGAllGlobalVariables
@server varchar(255),
@PkgName varchar(255), -- Package Name (Defaults to most recent version)
@UserName varchar(255) = Null, -- User Name if using SQL Security to load Package
@ServerPWD varchar(255) = Null,-- Server Password if using SQL Security to load Package (UID is SUSER_NAME())
@IntSecurity bit = 0,-- 0 = SQL Server Security, 1 = Integrated Security
@PkgPWD varchar(255) = '',-- Package Password
@chrAssignChar varchar(2) = '=',-- separator between the name and value in gvString
@chrSearchFor varchar(5) = '~', -- separator between gvStrings
@GlobalVString varchar(511) = NULL -- Optional Global Variables String ([GlobalVariableName=GlobalVariableValue~..])
-- 'gv1=1~gv2=20020411~gv3=chrValue'
AS
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)
DECLARE @GVName varchar(255), @GVValue varchar(255)
Declare @intPos INTEGER
Declare @gvString varchar(511)
Declare @strSearchIn varchar(511)
Declare @nCnt Integer
Declare @intRet Integer
DECLARE @CRLF char(2)
SET @CRLF = char(13) + char(10)
SET @intRet = 0
-- Create a Pkg Object
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUTPUT
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @oPKG, @hr
SET @intRet = 1
RETURN @intRet
END
-- Evaluate Security and Build LoadFromSQLServer Statement
IF @IntSecurity = 0
BEGIN
IF @UserName IS NOT NULL
SET @Cmd = 'LoadFromSQLServer("' + @server +'", "' + @UserName + '", "' + @ServerPWD + '", 0, "' + @PkgPWD + '", , , "' + @PkgName + '")'
ELSE
SET @Cmd = 'LoadFromSQLServer("' + @server +'", "' + SUSER_SNAME() + '", "' + @ServerPWD + '", 0, "' + @PkgPWD + '", , , "' + @PkgName + '")'
END
ELSE
SET @Cmd = 'LoadFromSQLServer("' + @server +'", "", "", 256, "' + @PkgPWD + '", , , "' + @PkgName + '")'
EXEC @hr = sp_OAMethod @oPKG, @Cmd, NULL
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN 1
END
IF @GlobalVString IS NOT NULL
BEGIN
SET @strSearchIn = @GlobalVString -- 'gv1=1~gv2=20020411~gv3=Needle'
Set @intPos = 1
Set @nCnt = 0
WHILE (@intPos > 0)
BEGIN
SET @intPos = CHARINDEX(@chrSearchFor, @strSearchIn)
If @intPos > 0
BEGIN
SET @nCnt = @nCnt + 1
SET @gvString = LEFT(@strSearchIn, @intPos-1)
SET @strSearchIn = Right(@strSearchIn, Len(@strSearchIn)- @intPos)
SET @GVName = LEFT(@gvString, CHARINDEX(@chrAssignChar, @gvString)-1)
SET @GVValue = RIGHT(@gvString, LEN(@gvString) - CHARINDEX(@chrAssignChar, @gvString))
--PRINT 'gv ' + @GVNAME + ': (' + @GVVAlue + ') '
SET @Cmd = 'GlobalVariables("' + @GVName + '").Value'
EXEC @hr = sp_OASetProperty @oPKG, @Cmd, @GVValue
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN 1
END
END
ELSE
BEGIN
SET @gvString = @strSearchIn
SET @GVName = LEFT(@gvString, CHARINDEX(@chrAssignChar, @gvString)-1)
SET @GVValue = RIGHT(@gvString, LEN(@gvString) - CHARINDEX(@chrAssignChar, @gvString))
--PRINT 'gv ' + @GVNAME + ': (' + @GVVAlue + ') '
SET @Cmd = 'GlobalVariables("' + @GVName + '").Value'
EXEC @hr = sp_OASetProperty @oPKG, @Cmd, @GVValue
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN 1
END
END
END
END
-- Execute Pkg
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @oPKG, @hr
SET @intRet = 9
-- RETURN @intRet
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 0
GO
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply