In the DTS package, how do I change the file location dynamcially? I
need file location to be changed depending on what server the DTS
package running on. I am using @@servername for this purpose.

This DTS package is very simple. It is importing a TEXT file into a SQL
Server table. Currently, the path of the file is hard coded. Is there a
way I can replace the hard coded path to @Path (which holds the value)
variable? See the code below.

Thanks for your help.


select @Path =
case @@servername
when 'A' then '\\server1\folder1\Myfile.txt'
when 'B' then '\\server2\folder2\Myfile.txt'
when 'C' then '\\server3\folder3\Myfile.txt'
end


*** Sent via Developersdex http://www.developersdex.com ***

You have to use Dynamic properties

A Panchanathan

Can you please exalpin how?

I was thinking to use this SQL code in the "Execute SQL Task" and pass
the result to "Source Text File".


select @Path =
case @@servername
when 'A' then '\\server1\folder1\Myfile.txt'
when 'B' then '\\server2\folder2\Myfile.txt'
when 'C' then '\\server3\folder3\Myfile.txt'
end


Thanks for your help.

*** Sent via Developersdex http://www.developersdex.com ***

Hello Test,

There are a few ways you can do this;

1. The Dynamic Properties task to read the value from an external source
(ini file, query etc)
2. Using DTSRUN and the /A switch to pass the value to a global variable
and assign that to the DataSource property in code
3. If using a programming language like C# to execute the package then you
can alter the values in code.

look on our site for examples (www.sqldts.com)

Allan

Allan,

Thanks for your help. This is what I've done so far:

1. Created a stored proc 'get_path' that gives me the path of file.
2. Created a global variable 'path' in the dynamic properties task in
the DTS pkg. (I already have a dynamic properties task defined in the
DTS with a global variable 'servername' set up)
3. This is how I am executing the DTS pkg using DTSRUN with /A:

create table #temp(path varchar(150))
insert into #temp exec up_get_file_path
declare @path varchar(150)
set @path = (select path from #temp)

declare @dtsrun varchar(1000)
set @dtsrun = 'dtsrun /F"d:\Myfolder\MyDTS.dts" /N"MyDTS"
/AServerName:8=' + @@servername + ' /APath:8='+ @path + 'File1.TXT'

exec master.dbo.xp_cmdshell @dtsrun

It is working fine but I don't like #temp table and a stored procedure
calls in this code. I was hoping to handle this inside the DTS package
or some other way. Is that possible? I would appreciate your thoughts on
this.

Thanks.





*** Sent via Developersdex http://www.developersdex.com ***

I found a way to avoid #temp table. This is what I am doing now -

declare @path varchar(150)
exec dbo.get_path @path OUTPUT

declare @dtsrun varchar(1000)
set @dtsrun = 'dtsrun /F"d:\Myfolder\MyDTS.dts" /N"MyDTS"
/AServerName:8=' + @@servername + ' /APath:8='+ @path + 'File1.TXT'

exec master.dbo.xp_cmdshell @dtsrun

It looks much better now.



*** Sent via Developersdex http://www.developersdex.com ***