hello i got trouble with Path destination of SSIS log provider for Text files
at first i use the path static like D:\\ . but right now I want to create the path dynamically , whic i get from SQL table , and put the value on variable
D:\\
@[User::LogPath]
so i create expression builder for SSIS txt File connection string
@[User::LogPath]+ @[System::PackageName]+ ("_"+(RIGHT((DT_WSTR,4) DATEPART("yyyy",GetDate()),4)+ RIGHT("0"+(DT_WSTR , 2) DATEPART("mm", GetDate()),2)+ RIGHT("0"+(DT_WSTR,2) DATEPART("dd",GetDate()),2)+"_"+ RIGHT("0"+(DT_WSTR,2) DATEPART("HH",GetDate()),2)+"_"+ RIGHT("0"+(DT_WSTR,2) DATEPART("mi",GetDate()),2)+"_"+ RIGHT("0"+(DT_WSTR,2) DATEPART("SS",GetDate()),2))+".log")
but the
I have this set up and running in all of my SSIS packages, because I like to keep everything table-driven.
In each package I have created a string variable called "LoggingPath," which is populated with a SQL Task using a select statement like the following:
SELECT FilePath FROM Files WHERE FileFunction = 'Log' AND SSISPackage = 'ImportStuff'
In this SQL Task the ResultSet is set to "Single row". For the Result Set I have the Result Name = 0 and Variable Name = "User::LoggingPath" referencing the before mentioned variable. I also have delay validation set to "True" for this SQL Task.
When the SQL Task runs it populates the variable that is then used in the following expression (similar to your expression) under the SSIS log text log file:
@[User::LoggingPath] + "\\" + @[System::PackageName] + "_" + (DT_STR,4,1252)DATEPART ( "yyyy" , @[System::StartTime] ) + RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , @[System::StartTime] ), 2) + RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , @[System::StartTime] ), 2) + RIGHT("0" + (DT_STR,4,1252)DATEPART( "hh" , @[System::StartTime] ), 2) + RIGHT("0" + (DT_STR,4,1252)DATEPART( "mi" , @[System::StartTime] ), 2) + RIGHT("0" + (DT_STR,4,1252)DATEPART( "ss" , @[System::StartTime] ), 2) + ".log"but the log file did'nt create?