running a DTS package from Query Analyzer
running a DTS package from Query Analyzer
(OP)
I have a DTS called myDTS wich has one input parameter called inputParam, I am trying to run it from query analyzer and wrote this query
EXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUT
if @hr <> 0
BEGIN
print 'error create DTS.Package'
RETURN
END
EXEC @hr = sp_OAMethod @object, 'LoadFromSqlServer', NULL,
@ServerName='myServer', @PackageName='myDTS', @Flags=256
IF @hr <> 0
BEGIN
PRINT '*** Load Package failed'
END
what is wrong with the above code and how can excute the package with the input parameter?
Thanks
CODE
EXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUT
if @hr <> 0
BEGIN
print 'error create DTS.Package'
RETURN
END
EXEC @hr = sp_OAMethod @object, 'LoadFromSqlServer', NULL,
@ServerName='myServer', @PackageName='myDTS', @Flags=256
IF @hr <> 0
BEGIN
PRINT '*** Load Package failed'
END
what is wrong with the above code and how can excute the package with the input parameter?
Thanks
Talk To Other Members
RE: running a DTS package from Query Analyzer
You need to think about the DTS object model to do this.
From VB you'd use this to add a global variable =
CODE
So, I think you'd need to use sp_OAGetProperty to retrieve the GlobalVariables collection, and then use sp_OAMethod to execute the AddGlobalVariable method on this object.
Here's a start (not tested at all, but try adding it after what you've already got)
CODE
declare @object2 int
EXEC @hr = sp_OAGetProperty @object, 'GlobalVariables', @object2 OUTPUT
IF @hr <> 0
BEGIN
PRINT 'Cant Get Global Vars'
END
EXEC @hr = sp_OAMethod @object2, 'AddGlobalVariable', @Name = 'myVarName', @Value = 'myVarValue'
IF @hr <> 0
BEGIN
PRINT 'Cant Add Global Var'
END
Hope this helps,
Alex
----signature below----
Majority rule don't work in mental institutions
My Crummy Web Page
RE: running a DTS package from Query Analyzer
RE: running a DTS package from Query Analyzer
@ServerName='myserver', @PackageName='mypackage', @Flags=256
IF @hr <> 0
BEGIN
PRINT '*** Load Package failed'
END
how do i use my windows login without mentioning my user name and password
(window login has DBO access)
THanks
RE: running a DTS package from Query Analyzer
Are you intending for this package to actually be executed by users from Query Analyzer, or from a different Client Application (of the home-grown variety)?
----signature below----
Majority rule don't work in mental institutions
My Crummy Web Page