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"

• Edited by Michael_SQL Saturday, September 05, 2015 12:17 AM

Hi Michael,

I am curious what made you decide on this laborious approach.

Typically it is one config file entry and done with it.

You can share the same config between all the packages.

Arthur, the reason is because I need my packages to be very dynamic.  They will be running against different servers, depending on the values of the variables specified at run time.  I further would like for my log files to reflect this "dynamisity" (I don't think it's even a word), so the location of the log files also needs to change accordingly.  The configuration file will not allow me to be quite as flexible.  I hope this makes sense.

Thank you!

Hi Michael_SQL,

After testing the issue in my environment, I can reproduce the first scenario. In the second scenario that modify the value of the [User::Log_Path] variable in the parent package to "C:\temp\Log_Run_Time", it only creates two log files for Parent Package in C:\temp\Log_Run_Time folder, one log file for Child Package in C:\temp\Log_Design_Time folder. Please double check this scenario.

After removing the original "Log_Design_Time" directory, the issue that child package will start failing complaining that it cannot find the path specified is correct behavior. Because the Child Package still store log file to C:\temp\Log_Design_Time folder, you just modify the value of the [User::Log_Path] variable in the parent package.

As to the issue that creating two log files for Parent Package, it is my pleasure to help you to reflect your recommendation to the proper department for their consideration. Please feel free to submit your situation on our product to the following link https://connect.microsoft.com/SQLServer/. Your feedback is valuable for us to improve our products and increase the level of service provided.

Thanks,
Katherine Xiong

Hi Michael,

First create log file for log file provider with Create File option,

then set the connection string using expression,

then delete that existing file earlier created ,

then set delayvalidation=false

and then execute package, you will get only one file created.

Thanks

Supriya

Thank you both Katherine and Supriya for taking the time to look into it!

Supriya, I double-checked that my packages and connection managers are using "Create File" option (FileUsageType=1).  The two files absolutely get created in my case and Katherine confirmed that as well.  It seems that DelayValidation setting does not matter.  I tried various permutations.

Katherine, I re-confirmed that I have two log files created for both the parent the child packages.  I provided screenshots below showing this.  Actually, the fact that I have two log files created for the child package is the biggest problem (because the child package is the one creating "extra" log file in the "Design" directory).  If I can figure out how to avoid this, I would be ok. Here are the screenshots again.

Before the execution (you can also see the log file connection manager settings)

After the execution:

If you email me personally (michael.neymit@blackline.com) I can send you a zipped SSIS solution.

Thank you very much!

• Edited by Michael_SQL 14 hours 5 minutes ago

Thank you both Katherine and Supriya for taking the time to look into it!

Supriya, I double-checked that my packages and connection managers are using "Create File" option (FileUsageType=1).  The two files absolutely get created in my case and Katherine confirmed that as well.  It seems that DelayValidation setting does not matter.  I tried various permutations.

Katherine, I re-confirmed that I have two log files created for both the parent the child packages.  I provided screenshots below showing this.  Actually, the fact that I have two log files created for the child package is the biggest problem (because the child package is the one creating "extra" log file in the "Design" directory).  If I can figure out how to avoid this, I would be ok. Here are the screenshots again.

Before the execution (you can also see the log file connection manager settings)

After the execution:

If you email me personally (removed email) I can send you a zipped SSIS solution.

Thank you very much!

• Edited by Michael_SQL 15 hours 12 minutes ago

Hi Michael_SQL,

I have send the solution about the second scenario, please check it.

Thanks,
Katherine Xiong

Katherine, thank you once again for taking the time to look into this!  I replied to your email - basically your solution is missing the child package configuration, which specifies the childs Log_Path variable should get its value from the parent package variable by the same name.  Adding this configuration re-creates my issue with four log files.

Anyway, I think Ive 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 doesnt fully resolve the issue with the parent package, because I cant 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 dont understand why validation process (at least I think its validation process) creates those extra files using design-time values. This just seems like a wrong behavior.

Thank you!

• Marked as answer by Michael_SQL 9 hours 54 minutes ago

I think you should set DelayValidation=True and then try .