0

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 the path i want to get the path from sql table and put it on variable,

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

Sabilv
  • 602
  • 1
  • 15
  • 44

1 Answers1

1

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) + ".csv"

Make certain you remember to apply the double backslashes "\\" as shown above. Hope this helps.

Kosh

  • I want to ask you, where do you create your expression? i create it on my file connection string expression of my logging file .. is it right? because i can't create the output – Sabilv Mar 13 '13 at 03:10
  • Yes, I also create the expression on the ConnectionString for the Logfile defined under Connection Managers. When I evaluate the expression it looks something like "D:\Logging\DataExtract_20130314131344.csv". Please check the arrow for my above response if it answered your question. Thanks. –  Mar 14 '13 at 19:07
  • Yes, I also create the expression on the ConnectionString for the Logfile defined under Connection Managers. When I evaluate the expression it looks something like "D:\Logging\DataExtract_20130314131344.csv". Note, LoggingPath = "D:\Logging" and PackageName = "DataExtract". Are you able to evaluate the expression? Make certain you have DelayValidation set to "True" for the log file. –  Mar 14 '13 at 19:16
  • the loggingpath on mine is came from dbase , when i evaluate expression, it didn't show.... – Sabilv Mar 18 '13 at 10:40