Hi, all Did anybody set up a SQL agent job to run a SSIS package without RDP onto the target SQL box? Say, I do the SSIS 2008 development on Machine A, and the SQL database engine and SSIS service are on Machine B. I can log into the SSIS through SSMS, using a domain user account with ssisltduser, sqlagentuserrol. Thanks a lot Garry

Hello Garry, Yes you can run a package remotely even if your local machine does not have the SSIS installed. Please refer to this article: http://msdn.microsoft.com/en-us/library/ms403355.aspx Arthur My Blog

Thanks, Arthur, I am afraid it might be going too far to execute C# code to run the SSIS package. My real difficulty is, I am not allowed to be on the SQL/SSIS box, to set up everything locally. I have very limited permissions. It will be easy to set up a SQL agent job to run the SSIS package, if I can log into Machine B as an administrator. I just wanna know whether it is possible for me to do the same thing on Machine A. The SSIS package uses a configuration file, to store connection strings. The SQL Agent is running under a different user account. I can import the package into SSIS through SSMS, I can create a SQL agent job, and I already set protection level as DontSaveSensitive. But the job just fails with errors like Failed to find package, Login failed for SQL Agent user, rather than the credential I stored in the configuration file. Garry

Thanks, Arthur, I am afraid it might be going too far to execute C# code to run the SSIS package. My real difficulty is, I am not allowed to be on the SQL/SSIS box, to set up everything locally. I have very limited permissions. It will be easy to set up a SQL agent job to run the SSIS package, if I can log into Machine B as an administrator. I just wanna know whether it is possible for me to do the same thing on Machine A. The SSIS package use a configuration file. Garry

Well, you have to put your SSIS package into a job on the target SQL Server box. You don't have to schedule the job, you just need to create a job step that will execute your package. Then you can call it from any machine . See this post: http://blogs.technet.com/b/reeds/archive/2006/09/22/how-to-remotely-execute-an-ssis-package.aspx Arthur My Blog

Hi, Arthur, the point is how to "create a job step that will execute your package." successfully. The job always fails. Garry

You will NOT have to put the package on the SQL server itself. SSIS can (and will) execute packages STORED anywhere. The only thing you need is to create a Job, then you can issue sp_start_job commands to run it. If you're having problems creating a job that will run a package, it's usually because of one of two things: your package protection level is causing sensitive information in the package to be hidden so the package won't run properly at runtime, AND/OR your job isn't running under the right account. Now - why is it that you are having problems making a job that will execute your package? Talk to me now on

thanks, Todd, So a protection level as DontSaveSensitive, together with configuration file, still cannot ensure the user account in the configuration file being used? Creating a SQL Agent job from SSMS doesn't complain anything. If I create a job, which point to my local development folder, executing it will return error like "This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format." The problem is, if I create the job from my local machine with File System, the c:\\ will be the one on my local machine. I cannot to choose a local path on the SQL box. Garry

You seem to understand the protection level issue - yes, you're good with that combo. I think I understand where you're having the problem creating the job. That's a shortcoming of the SSIS tools in SSMS that I hate as well. If you aren't permitted to RDP to the server to use SSMS on the server, then there's no way to use SSMS to get at the server drives. Even so - you still do have some choices to make this work even if you want to store your packages on the server local filesystem. You can (or have your admins) create a share on the server that's accessible by you and the proxy you're using to run the Agent Jobs. You would only need read access to set up the Agent Jobs. You'd use YOUR SSMS to create the jobs, and use the UNC - not the local drive letter - to config the job. (That's why you'd only need read access - I'm assuming deployment to that location is controlled.) If you don't want to (or can't) get shares made, then you can't use SSMS. But you CAN make Agent Jobs using T-SQL: sp_add_job, sp_add_job_step, etc... that can reference server-local drives. Talk to me now on

You are right, Todd I have a UNC and proxy account, but still fail. And if I deploy the same packages to a SQL box where I can RDP on, and have sysadmin permission, it is working fine with use a local path, but fail with UNC. Is there anything special I should do, or do I need to encode the UNC (\\MMM\FFF) in the configuration file? Garry

Please check the below pointers 1. Check the UNC name is correct or not. 2. Make sure the SQLserver agent services acoount has permission to read the share folder which has the package.

Yes, Sorna 1. I can copy my packages into the UNC 2. I was told the SQLserver agent services acoount has permission to read the share folder Thanks

What is the error you are getting? Did you try running the package manually?Arthur My Blog

Error is ""Login for UUU failed" I guess domain user account is the real road block, since I was denied a SQL login for testing. Just got below from another thread http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/2c889d78-ada8-472f-80d6-0035e3e7a86c Garry