DTS and Stored Procedure passing parameters

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!

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
ServerName
DatabaseName
FileName

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
course.


********************************
Loading DTS package via sp_oacreate
Author Nigel Rivett
http://www.nigelrivett.net/sp_oacreateLoadDTSpackage.html
********************************

your answer is here:
https://www.experts-exchange.com/questions/20983145/Automating-changes-to-a-DTS-package.html

UM clarification there is one control table with ONE entry per Process
..
Jay

Thanks for pointing me to the right direction.