running a DTS package from Query Analyzer

[washaw]

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

 

[AlexCuse]

Please don't cross-post.

You need to think about the DTS object model to do this.

From VB you'd use this to add a global variable =

objDTS.GlobalVariables.AddGlobalVariable("gvName", "gvValue)

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)

--store handle for GlobalVariables collection here
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

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[washaw]

THank you Alex

 

[washaw]

EXEC @hr = sp_OAMethod @object, 'LoadFromSqlServer', NULL,
@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

 

[AlexCuse]

SQL Server 2000 doesn't really support impersonation. (SETUSER may work, but its' use is discouraged and to execute users need to be sysadmin or dbo)

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)?

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page