Contribute
1
Answer

Create Dynamic Path For SSIS Logging with SSIS log provider

Ask a question
Ask a question

   
Sabil Valdano

Sabil Valdano
11y
2.2k
1

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 

@[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 

@[User::LogPath]

didn' give the path value so the txt Log file didn't create , is it possible to create SSIS log provider for Text files Connection string dynamically?


here's what i've done :

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?


Answers (1)
Next Recommended Forum