November 10, 2008 at 10:13 am
Could someone please give me an idiots guide to scheduling SSIS packages.
Pardon my ignorance but I thought microsoft might have made this quite simple..........
I have several packages developed in BIDS - all with EncryptSensitiveWithUserKey. with a user account of DOMAIN\MyUser
I can execute all of these packages successfully from within BIDS.
I have then deployed the project to SQL Server. if i right click the package from the MSDB store and select run package, I can execute the packages successfully. Inspecting the log, there doesn't appear to be any errors.
When I create a schedule task, owned by DOMAIN\MyUser, run as SQL Agent Service Account and execute the package - the package fails with the following error:
Executed as user: PC687\SYSTEM. The package execution failed. The step failed.
This is all run on my PC with the account DOMAIN\Admin set as the user under which SSIS service runs and I have also added DOMAIN\Admin to the database and assigned it to the sysadmin fixed server role.
I've seen some stuff about Credentials etc, but surely this isn't that difficult??
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
November 10, 2008 at 10:54 am
From you workstation:
-Running a package in BIDS runs in an environment in which all components are allowed (Enterprise Edition of SSIS) and the packages are run using your NT credentials
-Running a package from Management Studio runs an SSIS package on your local machine using your local SSIS engine and whatever edition of SSIS you have installed locally (probably the developer edition). The package will use your NT credentials.
From your SQL Agent:
-Running a package stored on an SSIS server will run the package on the server the SQL AGENT is installed and running on (SSIS has not built in remote execution facility). It will run under the credentials specified for the SQL Agent or the Proxy Account (if you selected one in the job step). The dialog for selecting the package and configuration files uses your NT credentials, not those of the agent or proxy.
A good test is to rdp into the SQL Server the job agent is running on as the SQL Agent service account and run the package through Management Studio.
You should also turn on package logging (right-click in an empty area of the control flow of the package and choose logging) so you can see the actual error messages in the package - if the package gets that far.
November 11, 2008 at 2:29 am
thanks - I give this a go.
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
December 3, 2008 at 9:58 am
Not sure if you've resolved this or not but I've had the same issue and have found the following from various sources:
The default setting for the package ProtectionLevel is EncryptSensitiveWithUserKey which means that when you deploy and try to run it under a scheduled job, it's going to fail if the jobstep is executing under a different login than the package CreatorName - which is probably most of the time. Changing this property to DontSaveSensitive will remedy this problem.
However, if you really need to store passwords for some connection string (ftp site, etc), you can enable Package Configurations and create an xml file or a sql table to store those values which will be read at runtime. Brian Knight has a video on on this topic.
Another approach, I believe, is to deploy into SQL Server instead of the FileSystem - but I haven't done that.
Hope this helps..
December 4, 2008 at 2:22 am
Hi Mark, thanks for the reply.
I'll check out that video as it sounds like it might be useful.
I have resolved this though (forgot to add another post entry). Basically, I found somewhere that I needed to set up a SQL credential under security for the user that the process should run as, and then then configure a proxy account for SSIS Package Execution and set the Scheduled Job step to execute as the proxy account.
Taking this approach worked.
My main problem, although not immediately obviously - due to the crap error message - was network access to another machine that was required for accessing an ODBC license file, for a successful ODBC connection.
(also, if it's of any interest - this was done with packages deployed to SQL Server.
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
December 5, 2008 at 1:59 am
If your package is saved to sql server msdb rather than file system then you can make life simple and set "Rely on server security" rather than Encrypt sensitive or not saving. This uses normal sql database security to control access.
December 5, 2008 at 2:13 am
Hi
U can also use the Protection Level "EncryptSensitiveWithPassword". That way u set a password for the package and it will ask you that password every time you open the package. It's a little less secure than the EncryptWithUserKey but it rips that kind of problems.
December 5, 2008 at 3:03 am
P Jones (12/5/2008)
If your package is saved to sql server msdb rather than file system then you can make life simple and set "Rely on server security" rather than Encrypt sensitive or not saving. This uses normal sql database security to control access.
I looked at doing this, but maybe I didn't set it up correctly.
the source files were in a file system, and published to an SQL server server. so when I attempted to save the packages to rely on SQL server security it prompted and error. I guess the source needs to be stored on SQL server, but I could see where or how to do that.
On the topic of setting package passwords, i found very irrittating that it asked me for the package password for every Execute Package Task in each package.
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply