February 23, 2009 at 10:18 am
I don't know if you've gotten your problem solved yet or not, but just so you have something to try - consider the following:
Have you looked into the ProtectionLevel of the package? Do a search on the forum here for ProtectionLevel or EncryptSensitiveWithUserKey. The root of this problem has to do with the fact that MS set the default property to EncryptSensitiveWithUserKey, which means if the account you intend to run the package under as a job is not the same account as the person that created the package - you could have some issues. Solution here is to change every .dtsx page so the ProtectionLevel is set to DontSaveSensitive. Doing this will mean that you will need to enable Package Configurations and provide various connection string info for things like ftp passwords, Initial Catalog or ServerName, etc. There are some good examples of this here and on http://www.jumpstarttv.com (by Brian Knight).
Another thing to look at is how you are calling the job step in Agent. One of the suggestions that was put out as a Best Practice for SSIS at a recent SQL Connections conference was to run the job step as an Operating System (cmdexec) type instead of SQL Server Integration Services type. For the Run As selection - use a Proxy Account based upon the proper Credential (again look at http://www.JumpStartTv.com for examples). The final step here is the Command window. Paste in the required command line code to run the dtsx package. Example: dtexec.exe /FILE "C:\Program Files\Microsoft SQL Server\90\DTS\Packages\MyPkgName.dtsx" - followed by any additional switches, etc. This is the same code that is generated when you double click on a SSIS package in Explorer and then go through each option, making the appropriate selections. The final option, displays the command line string that you've built up from all the above selections. It is this code that you would paste into the Command window for your Job Step - preceded by the "dtexec.exe" command.
Good luck, hope this helps.
Mark
February 23, 2009 at 10:34 am
So then I tried to create a proxy for SQL Server agent with a credential. I even gave the credential my domain account user and password to use (the one that works when I run it manually) just to see what would happen, but no go.
You are on the right track but it will not run without admin level user permissions both in SQL Server and the network level because when the Agent is permforming tasks in network folder Microsoft wants such tasks going back to someone with admin level permissions. Check the thread below for details.
http://www.sqlservercentral.com/Forums/Topic661486-148-1.aspx
Kind regards,
Gift Peddie
February 24, 2009 at 9:09 am
I had the same issue; Try http://support.microsoft.com/kb/918760 for the answer
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
Viewing 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply