Solved

DTS and Stored Procedure passing parameters

Posted on 2004-09-15
9
1,247 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 9

Accepted Solution

by:
apirnia earned 300 total points
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 9

Expert Comment

by:apirnia
Comment Utility
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
Comment Utility
0
 
LVL 10

Assisted Solution

by:Jay Toops
Jay Toops earned 200 total points
Comment Utility
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
Comment Utility
UM clarification there is one control table with ONE entry per Process
..
Jay
0
 
LVL 1

Author Comment

by:IceMan713
Comment Utility
Thanks for pointing me to the right direction.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now