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 2008 Forums  SSIS and Import/Export (2008)  Remote SSIS execution with config variable

Author  Topic  Bytheway Starting Member 1 Post Posted - 2010-06-10 : 07:42:29 I am attempting to run an SSIS Package (via a SQL Server Agent Job) on another server and pass indirect configuration-file information but the wrong configuration file is used. Step 1: Execute batchfile on the calling server setting a System-variable to identify the configuration file location on called server (SSIS package uses indirect configuration) rem Set System-variable for config file location SETX Out_SSIS_ConfigFile \\remote_servername\C$\required_configfile.dtsConfig -m Step 2: In the batchfile, launch the SSIS via a SQL Server Agent Job on the remote server SQL server "\\remote_servername\C$\Program Files\Microsoft SQL Server\100\Tools\Binn\osql.exe" -E -Sremote_servername -i\\remote_servername\C$\Out_StartJob.txt Note 1: Out_StartJob.txt contains: USE MSDB GO EXEC sp_start_job @job_name = 'SSIS_Call_Jobname', @server_name = 'remote_server_SQL_Server_Instance_name' GO Note 2: 'SSIS_Call_Jobname' has a single step of type 'SQL Server Integration Services Package' which executes the package dtsx file (in the file system of the remote server). 'Configuration' and 'Command File' tabs are empty. The SSIS runs OK but uses the wrong configuration file. It would appear that it is referencing the System-variable of the same name (Out_SSIS_ConfigFile) on the remote server rather than that of the calling server to locate the config file required. I guess this is logical, since osql is being run on the remote server. Any idea how I can pass the correct information to the job, since as far as I am aware indirect configuration requires a system- not user-variable. Perhaps a different approach to remotely launching the SSIS, with or without indirect configuration, would be preferable. At present I'm stuck!