DTS and Stored Procedure passing parameters

Hi Experts,

I have a DTS package (btw, using SQL 2000) stored in Local Package.  I need to call this package from a stored procedure and on top of that, in my sp, I have a parameter I need to pass as a global variable to the package.  Can this be done?  Thanks.

Who is Participating?
apirniaConnect With a Mentor Commented:
1) exec master..xp_cmdshell 'DTSRun /S (local) /U sa /P  /N myPackageName', NO_OUTPUT


2) create a job to run DTS, then exex sp_start_job @job_name='job_name'

Add a Dynamic Properties Task to your DTS - that lets you prompt for a parameter. But I do not know what happens if you then call this DTS from an SP.
YES -  can be done, but it is a very complicated process.  Unless you're familiar with GlobalVariables,  I wouldn't recommend heading in that direction.  EE might be able to come up with something else for you if you can give a little more detail on what you want to accomplish.  Good Luck!
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Two other approaches: depending on which program is the "controlling" program, have that program accept parameters and put them into a table. Then reference that table in your SQL.

The global variables in DTS are not that big a deal, though they can be quirky. I have used them a lot.
Just remember, in order to execute xp_cmdshell, you must be a member of the sysadmins group OR, you must have the proxy account enabled on SQL Agent to allow non-sysadmins the ability to run the command.

The following might help you too......

This is written for sql server v7
This can be used for setting the server/database or any other values needed.
A client application language (e.g. VB) is preferred for a production system but this can be useful for testing.

Global Variables for the package

declare @objPackage int
declare @PackageName varchar(128)
declare @rc int
declare @ServerName varchar(128)
declare @DatabaseName varchar(128)
declare @FileName varchar(128)

      select       @PackageName = 'Data Import Package' ,
            @ServerName = @@ServerName ,
            @DatabaseName = db_name() ,
            @FileName = '\\MyPC\InpFile\TestFile.txt'

      exec sp_OACreate 'DTS.Package', @objPackage output
      exec @rc = sp_OAMethod @objPackage, 'LoadFromSQLServer' , null,
            @ServerName = @ServerName, @Flags = 256, @PackageName = @PackageName
      exec @rc = sp_OASetProperty @objPackage,
            'GlobalVariables("ServerName").value', @ServerName
      exec @rc = sp_OASetProperty @objPackage,
            'GlobalVariables("DatabaseName").value', @DatabaseName
      exec @rc = sp_OASetProperty @objPackage,
            'GlobalVariables("FileName").value', @FileName
      exec @rc = sp_OAMethod @objPackage, 'Execute'
      exec @rc = sp_OADestroy @objPackage

You would need to test the return code between each of these statements of

Loading DTS package via sp_oacreate
Author Nigel Rivett

Jay ToopsConnect With a Mentor Commented:
ALL of that wonderfully complex stuff aside
this is the way to do it ..

I simply insert my variables into a CONTROL table that i create for each DTS
process i run.
then kick off my DTS process.
This works beautifully
and you can update this table with status messages about your process

Jay ToopsCommented:
UM clarification there is one control table with ONE entry per Process
IceMan713Author Commented:
Thanks for pointing me to the right direction.
All Courses

From novice to tech pro — start learning today.