1

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"

  • If you change ContainerStartTime to StartTime, does it behave as expected? – billinkc Sep 7 '15 at 23:56
  • Billinkc, changing to "StartTime" doesn't make a difference. – SQL_Guy Sep 8 '15 at 16:01
1

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"

  • Very interesting! I am working on a different project right, but will try your suggestion when I have the opportunity! Thank you for your feedback. – SQL_Guy Jan 7 '16 at 16:04
0

I think I’ve figured it out (at least partially). It seems that in order to avoid creation of duplicate log file for the child package the value of “Log_Path” variable in the “child” package have to be made blank. If there is no value, the validation process will not create an extra file and will properly inherit value specified in the “parent”. This still doesn’t fully resolve the issue with the “parent” package, because I can’t run it from the development environment without any value specified for the “Log_Path” variable. The only way I found around that is to make it blank, save it and then execute it from the command line (DTExec) while passing the desired variable value via SET option. This finally results in just two files instead of four.

I still don’t understand why validation process (at least I think it’s validation process) creates those “extra” files using design-time values. This just seems like a wrong behavior.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.