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

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.

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

[quoted text, click to view]

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.

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.