Solved

DTS and Stored Procedure passing parameters

Posted on 2004-09-15
9
1,295 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 43

Expert Comment

by:Eugene Z
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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

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…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

734 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