Passing a variable into a DTS Package

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Passing a variable into a DTS Package

Author

Topic

jdoering
Starting Member

32 Posts

Posted - 2002-06-18 : 13:18:16

I am working in SQL 2000. I have defined a global variable in my DTS package. I would like to run my DTS from a stored procedure. However, I would like to pass a variable into my stored procedure, that then passes the variable into the DTS package. The code for my procedure is below. My question is how and where do I placed the variable and in what syntax?

CREATE PROCEDURE eCSUpload_Products
@sSupplierName as varchar(35)
AS

Exec master..xp_cmdshell 'DTSRun /SPOSEIDON /Usa /Psa /NLoad_Products_V1' @sSupplierName???????

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Any help would be greatly appreciated. I need to keep all this in a stored procedure because I am working with .NET & ASP

Thanks,
Julie

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-06-18 : 13:34:38

BOL will tell you everything you need to know

setBasedIsTheTruepath
<O>

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-06-18 : 18:06:58

Just a little tip - to easily generate DTSRUN command strings use the dtsrunui utility (BTW I hope your sa password isn't sa

)

Goto Start/Run and type in dtsrunui and hit enter
This brings up the DTS interface, select server and package and then click on advanced, set any global variable values and then click the
generate button. You can then copy and paste this command into QA. I would however remove the version switch it tends to put in there (/V)

And since I'm feeling generous
'DTSRun /SPOSEIDON /Usa /Psa /NLoad_Products_V1 /A "gvName":"8"="' & @sSupplierName & '"'

Replace the & with a plus sign (it doesn't seem to show up ?)
[How the Hell do you put a plus sign in a post ????]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources