Search

onze sponsors

microsoft_logo.gif


 

computrain_logo.JPG

Forum Login | Register
   Forum

 

Subject: Remote SSIS execution with indirect config file
Prev Next
You are not authorized to post a reply.

Author Messages
David BythewayUser is Offline

Posts:1

10-06-2010 12:59:10 Alert 

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 the called server (SSIS package uses indirect configuration) as follows:

rem Set System-variable for config file location
SETX Out_SSIS_ConfigFile \\remote_servername\C$\required_configfile.dtsConfig -m

Step 2: In the batchfile, use osql to 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 of course). 'Configuration' and 'Command File' tabs are empty.

Result:
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. 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!

You are not authorized to post a reply.
Forums > Forums > Business Intelligence > Remote SSIS execution with indirect config file



ActiveForums 3.6
  
Copyright (c) 2012 PASS Nederland   Privacy Statement  Terms Of Use