March 2, 2008 at 11:09 pm
Hi All,
Overview:
We are using SQL 2005 with SSIS and have created a set of packages which load data from txt. files into our DW staging tables and then from our staging tables into our star schemas. The packages used variables that are stored in xml config files. The parent package calls and executes each package in sequences and the child packages inherit variables from the parent.
Issue:
One of the variables set in the config file specifies a directory to write the log files into and SSIS logging is set to use SSIS Log provider for Text files and with a File_log connection using Create file and an expression using the log directory variable for each package.
When I execute the packages as a SQL Agent job, it creates logfiles before picking up the variable which specifies the desired location for these logfiles. The location where the logs are written to is the WINDOWS\system32 directory. If I run the job under an account with sufficient permissions, a set of log files are created within the WINDOWS\system32 directory as well as the intended log file directory and the job executes successfully. However log files building up in the WINDOWS\system32 directory and using an account with those permissions is not a long term option. Executing the package straight from the SQL Server- by right-clicking on the package name in Management studio- does not write this second set of log files and also runs successfully, however this job is required to be scheduled each night hence this is also not an option.
If I hardcode the logfile directory variable into the packages the unwanted 2nd logfiles are not created- however I do not want to hardcode my packages, I would like the execution to use the specified variable.
Has anyone come across this issue and/or has any ideas how to resolve it such as specifying a working folder on the command line?
March 5, 2008 at 3:06 pm
In case anyone suffers the same....
I've managed to resolve this problem:
The actual issue narrowed down to the fact that the child packages were not picking up the folder path from the parent package in time to write their log files to the appropriate directory. Apparently in sequence of execution, parent variables are last to be read.
I resolved this by removing the the parent logfile directory variables and adding the config file with folder directories variables directly to each child package. (nb: Each package was re-using the same config file and it did not affect execution or deployment if a package did not use all the variables in the config file even if it displayed validation errors in Visual Studio).
This resolved the issue and all logfiles were written to the correct directory as specified in the configuration.
More NBs:
- Other Parent variables such as batch id and execution_key and the other folder directories are passed fine in the same setup (all non-log file related)
- Not sure whether this issue occurs using Environment Variable instead of a config file as parent (that wasn't an option here).
May 7, 2012 at 9:22 am
I'm having a similar problem - that it looks for a Log directory under System32 - even though it's a simple stand-alone SSIS package, not parent and child. The first task in the package updates the log file path, and the connection to the log file has "DelayValidation = True". What's strange is that the package works fine when run interactively, but when run by SQL Server Agent as a job, it fails if folder Windows/System32/Log does not exist.
BUT, when that folder is created -- even though no log file is created and written to there -- the the SQL job succeeds.
Any ideas? THANKS!
Phil
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply