I have a simple SSIS package (stored in the file system) that gets a file path from a configuration file.The configuration type is an indirect XML configuration file that uses an environment variable to store the location of the configuration file.
When I run the package using dtexecui, or just dtexec from a command line, the package successfully picks up the file path from the configuration file (for verification I am writing out the variable containing the path to the log file).However when I run the package from a SQL Agent job it appears that the configuration file is not being used (the path is set to the same dummy path that I used during development).I have tried running the job as both a CmdExec and an Integration Services job and both fail on the same thing (invalid file path).
Both the SQL Server Agent service and the Integration Services service use a domain account as their start up account.This domain account has been included in the local administrators group on the server (in case it was having trouble accessing the environment variables).
What is the problem here – surely changing the way in which the package runs should not affect the configuration file settings!Any help would be appreciated…
Seems like it should work. Maybe your Security ProtectionLevel setting? It's not set to EncryptSensitiveWithUserKey, is it? And even so, I couldn't explain to you why that would cause the problem.|||
Are you using relative paths?
Sounds like you are.
Needs to be full path.
Can you show what the configuration values are?
|||The protection level on the package is set to DontSaveSensitive. I don't think that is the problem though because the configuration value is saved locally in an XML file not in a database...|||The configuration value I am using is not a relative path, it is a full path i.e. "D:\LandingArea\Daily\". In my SSIS package I call a number of "child" SSIS packages which are all set as relative paths. I know that this causes trouble so I use the PackagePath that I get from the configuration file to set the relative path to a full path. As I mentioned I am writing out the PackagePath to the log file to verify its value, and when I run the package using dtexecui it writes the correct path to the log file, however when I run it from SQL agent I get the path that was hardcoded in the package during development.
|||I did some more trouble-shooting for this issue. I created a small test package that has 1 variable and 1 Connection.The package has a script task in which it writes the values for both the variable value and connection string to a SQL log file – that is all that the package does (keeping it simple)...
What I discovered is that when using direct XML configuration files the package picks up the correct settings every time regardless of how the package gets executed.When using Indirect XML Configuration, the package picks up the correct configuration settings when running it using dtexecui or dtexec from the command line, however when running it from a SQL agent job (regardless of whether it is a CmdExec job or not) it ignores the configuration settings and uses whatever the last values were in development.The package does not seem to issue any warning message that it could not access the environment variable or configuration file!
Since I am logged on as local administrator and have system administrator rights on SQL I’m assuming that this is a bug and not by design!
|||Flamin,
Just in case; Jamie has blogged about a special behavior (issue?) when uisng indirect configuration.
http://blogs.conchango.com/jamiethomson/archive/2005/10/31/2336.aspx
Rafael Salas
I am sure that I logged off after creating the environment variables but just to be safe (and because I had the opportunity to) I rebooted the server - VOILA it all works now! Thanks Rafael!
sqlsql
No comments:
Post a Comment