A Question For DTS Gurus!! - SQL Server Forums

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)

A Question For DTS Gurus!!

Author

Topic

jdoering
Starting Member

32 Posts

Posted - 2002-08-06 : 13:42:28

I am working in SQL Server 2000. I am passing a variable into a stored procedure and then passing the variable into a DTS package from the DTS Command Line within query analyzer as such:

ALTER PROCEDURE eCSUpload_Products
@Supplier_ID varchar(25)
AS
Exec master..xp_cmdshell 'DTSRun /SZeus /Usa /Pecompany /NLoad_ProductsforSuppliers /A "Parameter 1":"8"="@Supplier_ID"'

The code runs without any errors except I do not believe that the DTS package is receiving the variable & using the variable.

Any suggestions would be greatly appreciated.

Thanks,
Julie

Edited by - jdoering on 08/06/2002 13:57:22

KHeon
Posting Yak Master

135 Posts

Posted - 2002-08-06 : 14:20:46

Here is the definition from SQLBOL on the /A argument:

quote:

/A global_variable_name:typeid=value

Specifies a package global variable, where typeid = type identifier for the data type of the global variable. The entire argument string can be quoted. This argument can be repeated to specify multiple global variables. See the Remarks section for the different available type identifiers available with global variables.

To set global variables with this command switch, you must have either Owner permission for the package or the package must have been saved without DTS password protection enabled. If you do not have Owner permission, you can specify global variables, but the values used will be those set in the package, not those specified with the /A command switch.

My first suggestion would be to not use a variable with a space in it (ie: Parameter 1), that isn't valid in any language that I know of, the space could be the problem. Additionally, concatenating the @SupplierID to the string might be getting interpretted literally instead of the way you are expecting. You could try creating a variable and dynamically building the string (for execution). For instance:


DECLARE @ARGS VARCHAR(1000)
SET @ARGS = 'DTSRun'
SET @ARGS = @ARGS + '/SZeus /Usa /Pecompany /NLoad_ProductsforSuppliers '
SET @ARGS = @ARGS + '/AParameter1:8=' + CAST(@Supplier_ID AS VARCHAR(10))
EXEC master..xp_cmdshell @ARGS

Now, I must ask why you are using a stored procedure to call a DTS package?

Yes, I've done it too, for fun, even added it to a trigger just to see if it something that can be done. It is, but is woefully slow. Is this a sproc that will be called frequently? Unless I'm mistaking triggers with the behavior of a sproc, the sproc will wait on the DTS package to finish before it can continue (this is definetly true when used inside a trigger). If this is a frequently called sproc my suggestion would be to find a way to store this information in a "queue" table and have a DTS package execute every so often (frequency will depend on your needs) that processes the queue table. This method works pretty good, especially if your system can afford to not be completely real-time.

For instance, I use this in a system I wrote for my website, which in essence publishes dynamic (new, edited, deleted) content from the database to a static html file (for performance reasons).

Sure hope I've made sense here. Good luck.

Kyle Heon
PixelMEDIA, Inc.
Senior Application Programmer, MCP
kheon@pixelmedia.com

Edited by - KHeon on 08/06/2002 14:25:17

jdoering
Starting Member

32 Posts

Posted - 2002-08-06 : 16:39:00

I need to use a store procedure to call a DTS package because I am uploading an Excel spreadsheet into a sql server table using the DTS package. I need to make the store procedure to pass a certain parameter to concatenate this variable with one of the columns that I am uploading. It is confusing, but since SQL server does not support BULK Insert using Excel, I have to resort to using DTS in my application.

I will try out your suggestion

KHeon
Posting Yak Master

135 Posts

Posted - 2002-08-07 : 11:42:40

What application language are you writting your app with? If you are using VB you can hook into DTS packages pretty cleanly through VB, in fact, SQL Server allows you to save out your DTS package as a Standard Module (.bas) file which should aid in that integration. You might want to persue that avenue instead.

You should be able to do everything you want right inside your DTS package. I've written many packages in the past year which do all sorts of crazy things, one of recent creation even used MSMQ to communicate back to itself later in the scripts execution.

I don't understand fully your situation but I'm willing to bet that there is a cleaner, and possibly even easier way of doing what you want through DTS alone.

Kyle Heon
PixelMEDIA, Inc.
Senior Application Programmer, MCP
kheon@pixelmedia.com

jdoering
Starting Member

32 Posts

Posted - 2002-08-08 : 08:21:37

Kyle,

Thank you for your suggestion. It works!

KHeon
Posting Yak Master

135 Posts

Posted - 2002-08-08 : 10:51:28

Glad to be able to assist.

Kyle Heon
PixelMEDIA, Inc.
Senior Application Programmer, MCP
kheon@pixelmedia.com

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2003-12-01 : 09:43:51

Kyle,

Thx, I searched for some time to find the right notation on using the /A in DTSRUN.

Now I found it and it works...

Henri

~~~
Success is the ability to go from one failure to another with no loss of enthusiasm

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources