Solved

DTS and Stored Procedure passing parameters

Posted on 2004-09-15
9
1,280 Views
Last Modified: 2013-11-30
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.

Marc
0
Comment
Question by:IceMan713
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 10

Expert Comment

by:AaronAbend
ID: 12067214
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.
0
 
LVL 8

Expert Comment

by:MartinCMS
ID: 12067321
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!
0
 
LVL 10

Expert Comment

by:AaronAbend
ID: 12067395
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.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 9

Accepted Solution

by:
apirnia earned 300 total points
ID: 12067647
1) exec master..xp_cmdshell 'DTSRun /S (local) /U sa /P  /N myPackageName', NO_OUTPUT

or

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

0
 
LVL 9

Expert Comment

by:apirnia
ID: 12067675
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
********************************


0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 12067950
0
 
LVL 10

Assisted Solution

by:Jay Toops
Jay Toops earned 200 total points
ID: 12068064
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
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 12068075
UM clarification there is one control table with ONE entry per Process
..
Jay
0
 
LVL 1

Author Comment

by:IceMan713
ID: 12069254
Thanks for pointing me to the right direction.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question