Sql-server – SSIS package with log provider configured using expression – creates multiple log files, fails if I change original target folder

sql-server-2008sql-server-2008-r2ssis

I have created a small SSIS solution to illustrate my problem. Unfortunately it looks like I can't attach it here, so I will try to explain. I can send the solution to people individually.

SSIS SQL Server 2008. I have a package called "Parent_Package" with a single variable: "User::Log_Path". During design time I assigned this value to it: "C:\temp\Log_Design_Time". The package has a log provider configured (simple text file). The connection string of the log file has an expression: @[User::Log_Path] + "\" + @[System::PackageName] + [expression for timestamp] + ".txt". As you can see, I would like to create a new log file for each package execution.

If I now run this package, I will have two log files generated! One will have a timestamp from around the time I created the package. This file only has the header fields and no other other information. The second file has the correct timestamp and correct output information:

One run - two log files created (one with just header fields)

It appears that the first file gets created during validation (even though I set DelayValidation = True for the log file connection manager). So my first question is why is this happening? It doesn't seem like the right behavior.
Now to take this to the next step, I create a child package "Package_Child". I add an execute package task to the parent package to run this child package. In the child package I add a variable by the same name as above: "User::Log_Path". Next, I add a configuration to the child package, so that the value of "User::Log_Path" is obtained from the parent package variable by the same name. Finally I add log provider to the child package and set it up the same way as described above for the parent package.

Let's now run the parent package again. This time I will end-up with three log files: two for parent and one for child:

Parent package run - three output files created

For my next test, I now would like to change the location of the log files, so I modify the value of the [User::Log_Path] variable in the parent package to "C:\temp\Log_Run_Time". If I run now, I will have four log files! Two for the parent package in the "Log_Run_Time" directory and two for the child package – one in "Log_Design_Time" and one in "Log_Run_Time" directory:

Parent package run - change the Log_Path variable value
enter image description here

What's going on?
Finally, if I simply remove the original "Log_Design_Time" directory, my child package will start failing complaining that it cannot find the path specified:

Child package failure

Obviously it's still looking for the path specified earlier, but why? Again, I set "Delay Validation" = True on all my connection managers and the package itself.
I appreciate any help on this.

Thank you!

P.S. Here is a complete expression for log path: @[User::Log_Path] + "\\" + @[System::PackageName] + "_" +
(DT_STR, 4, 1252)DATEPART("yyyy", @[System::ContainerStartTime]) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mm", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("dd", @[System::ContainerStartTime]), 2) + "_" +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("hh", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mi", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("ss", @[System::ContainerStartTime]), 2) + ".txt"

Best Answer

I believe it has to do with the timing and interpretation of System Variables.

Using this expression to build a filename with _.log I had been using:

@[User::PathROOT]+"edw_logs\\"+ @[System::PackageName] + "_"+(DT_STR,4,1252) YEAR(GETDATE())+ RIGHT("00"+(DT_STR,2,1252) MONTH(GETDATE()),2)+RIGHT("00"+(DT_STR,2,1252) DAY(GETDATE()),2)+RIGHT("00"+(DT_STR,2,1252)DATEPART("Hour", @[System::StartTime]),2) + RIGHT("00"+ (DT_STR,2,1252)DATEPART("Minute", @[System::StartTime]) ,2) + RIGHT("00"+(DT_STR,2,1252)DATEPART("Second", @[System::StartTime]),2) +".log"

and two files were created once on validation and once when it ran.

Using just User variables, they are evaulated at runtime and only one file is created. See this:

@[User::PathROOT]+"edw_logs\\"+ @[User::MyPackageName] + "_"+(DT_STR,4,1252) YEAR(GETDATE())+ RIGHT("00"+(DT_STR,2,1252) MONTH(GETDATE()),2)+RIGHT("00"+(DT_STR,2,1252) DAY(GETDATE()),2)+RIGHT("00"+(DT_STR,2,1252)DATEPART("Hour", GETDATE()),2) + RIGHT("00"+ (DT_STR,2,1252)DATEPART("Minute", GETDATE()) ,2) + RIGHT("00"+(DT_STR,2,1252)DATEPART("Second", GETDATE()),2) +".log"

Related Question