Solved

DTS and Stored Procedure passing parameters

Posted on 2004-09-15
9
1,271 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query Syntax 17 36
Whats wrong in this query - Select * from tableA,tableA 11 32
TSQL query to generate xml 4 36
ms sql + get number in list out of total 7 29
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

831 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