INI File

Hello,

Like as I explained in my previous thread I decided to update SQL Server Connection Properties from the DTS Package with INI File.

The problem is the location of the file which is actually stored on the drive D: of the 3 SQL servers (D:\test.ini as example).
When the DTS runs the file is not found by the system which is looking for the file on the D: of the user's PC or the D: of the network server where the binary of the application is registed on. I don't know exactly where it is looking for but never on the local server in fact.

Is it possible for the location written in the DTS package for the INI file to map a network drive on the local SQL server ??? I can't use the name of the SQL server to make the reference to the INI file (ex : \\server1\toto\test.ini) because I don't want to modify each of them according to the SQL server where the DTS is registered on.

Each SQL server has a drive D so the INI files can be located on them.

Thanks in advance and sorry for my english :)

Why cant you store all the ini file in one file server say
\\Fileserver\inifiles\Server1.ini
\\Fileserver\inifiles\Server2.ini
\\Fileserver\inifiles\Server3.ini

For a very simple reason.

I'd rather create only one INI file "test.INI" and register it on the 3 SQL servers. I change the data in this file according to the SQL server but the name ("test.ini") and location ("D:\") are always the same.
With this solution, I'm not obliged to modify the value of the INI file's location when I transfer a DTS package between 2 SQL servers.

But this solution doesn't work apparently because I can't make a reference to a local drive of the SQL Server. I must write the name of the server as you wrote \\server1\...\test.ini

You can reference it as "D:\test.ini", but you have to run the DTS package locally on the SQL Server, not remotely on a different machine.

Yes that's what I understood so my solution can't work.

How can I read the data of a INI file in an ActiveX script ?

For example this is the content of my file test.ini :

[SERVER]
value=myServer

[NETWORK]
value=myNetwork

I'd like to get back the value of this 2 lines in 2 DTSglobalVariables.

Well, first you have to create 2 global variables and assign them the values in the ini file. Then you can refer to them as DTSGlobalVariables("VariableName").Value

So if your global variables are named "Server" and "Network", they would be DTSGlobalVariables("Server").Value and DTSGlobalVariables("Network").Value.

Yes thanks, I knew this solution but I can't use it to resolve my problem...

My DTS packages are not launched in local but with remote connections with the users' PC which can't access the SQL servers with their account, so the INI files can't be stored on the SQL servers.
And I have 3 differents servers with exactly the same DTS packages, so I don't want anymore to change the value of the files' and databases' connections in each of them according to the name of the server.

I'd rather, if it's possible, get back the name of the SQL server of the DTS package when I execute it without using of INI file or DTSGlobalVariable.

Are there any properties in a DTS package to know which SQL server they are executed from or the location of the DTSrun.exe which is launched them ????

Thanks in advance.

You could execute the DTS package from a stored procedure by using xp_cmdshell to run dtsrun.exe on the server. Before running the package get the server name by querying for @@ServerName and pass that into the DTS Package as a global variable value. You can set the value of global variables at run time in this manner.

OK thanks a lot.

I think I'm going to use this solution finally.

Haaaaaa !!!

I have a new problem using the command xp_cmdshell to launch my DTS packages.

With my own account no problem but to execute the procedure the users use a local SQL account.

And when the procedure tries to execute this line

exec master..xp_cmdshell @chaine

that doesn't work anymore...

The system gives me this error :

Msg 50001, Level 1, State 50001
xpsql.cpp: Error 1813 from GetProxyAccount on line 604

Have you got an idea about this problem ??
However the local account used to launch the procedure stored in the master database is DBO of this database.

You have to have sa permissions to use xp_cmdshell.

1. Create a global variable "ServerName"
2. Use dynamic property and assign the global variable to SQL Server connection
3. Create a job to execute this dts package as follows

DTSRUN /Stestvm /E /N"test3" /A"ServerName"="myserver"

Thanks everyboy.

Our SQL administrator found an option in the setup of the server to specify the command xp_cmdshell can be used by account without sa permissions.