Setting Import File Name for DTS

Question

Setting Import File Name for DTS

Scott Grant

SSC-Addicted

Points: 410
More actions
June 19, 2006 at 10:47 am

#173066

Hello,
I am attempting to set the file name in the dts procedure at run time. I have set up a global Variable gExcelFileName and am attempting to code the rundts in the stored procedure to run the dts with the passed excel file.
The stored procedure editor doesnt like any of the parameters i have coded into the dts it states "Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '/'."
The Dts Command is as:
DTSRun /S"MyServer" /U"UserId" /P"Pwd" /E /N"Load_CCI_MTD" /A"gExcelFileName":"8"=@ImportFileName
What am I missing?
Thanks
Scott
PS Running SQL2000 in Windows XP

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Erik Kutzler SSCarpal Tunnel Points: 4287 More actions · Answer 1

The DTSRun utility can't be executed directly in a SQL statement. You'll have to use the xp_cmdshell stored procedure to run it.

Also, if you're specifying the username & password, you don't need /E, which is used for trusted connections.

Something like this will do the trick:

USE master

GO

DECLARE @ImportFileName varchar(500)

SET @ImportFileName = 'c:\test.txt'

DECLARE @cmd varchar(1000)

SET @cmd = 'DTSRun /S "MyServer" /U "UserId" /P "Pwd" /N "Load_CCI_MTD" /A "gExcelFileName:8=' + @ImportFileName + ' " '

EXEC xp_cmdshell @cmd

Scott Grant SSC-Addicted Points: 410 More actions · Answer 2

Erik,

That works great! I just had to add a Dynamic Properties Task to my actual DTS job and got the passed value to work.

Thanks.

That will get my dts import files off of the Server, do you know what settings I need to adjust to have it look at my local pc for the files instead of the server for dts?

Thanks Again,

Scott

Luke L SSC-Dedicated Points: 35547 More actions · Answer 3

I may be incorrect with this, but I thought that the only way it would do that would be to make sure that you have a share on your local machine and have the DTS job pull the files down through a URL path.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Scott Grant SSC-Addicted Points: 410 More actions · Answer 4

I attempted the shared url \\servername\share\directory\file.xls

and the DTS could not find the file, unless the share was physically on my SQL Server server. Shares to other server directories did not work.

Scott