Executing SSIS Packages through SQL Agent

Last Post 02 May 2007 02:38 AM by Rocky Raccoon. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Rocky Raccoon
New Member
New Member

--
01 May 2007 10:17 AM
I have a package that imports data from one server to another. When I run the package manually I can connect to the source server using either my Windows account, the domain account that runs SQL AGENT, or a SQL Server login, all three work fine. When I execute the package from SQL Agent it only works if the package is set up to use Windows authentication. How can I get SQL Agent to use the SQL Server login that is defined in the package?
Rocky Raccoon
New Member
New Member

--
02 May 2007 01:46 AM
The job owner is sa. In the package I define the SQL Server Login and password as part of the source connection definition. I checked the 'Save My Password' option. When I execute the package directly I don't have to reenter the password.
Rocky Raccoon
New Member
New Member

--
02 May 2007 02:38 AM
Aha, I found some other postings on the internet and was able execute the package via the following: 1) Change the package ProtectionLevel to EncryptAllWithPassword. This password protects the entire package. (FYI - I had trouble finding the ProtectionLevel parm, you need to click on the white space under the Control Flow tab to see it. Right clicking on the package in Solution Explorer and selecting Properties only shows the name and the path.) 2) Edit the job step that executes the package, click OK and it will prompt you for the Package password. Thanks rm
You are not authorized to post a reply.

Acceptable Use Policy