Creating SSIS log file using expression and passing log location to a child package

Hello,

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 probably 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. 


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.


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:

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:

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"






September 4th, 2015 8:16pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics