When setting up a log file connection to be used as an SSIS log file provider if you statically declare the connection string the file will just grow and grow continuously. Depending on your scenario this file could become huge and make finding entries for a particular date and time difficult.
Fortunately we can use expressions to build the connection string dynamically and have SSIS write to a file whose name is based on the current date. To do this click on the file connection and from the Properties window add a ConnectionString expression to build a file name string with the date embedded. A sample expression for YYYY-MM-DD format is below.
“SSISLogFile-” + (DT_STR, 4, 1252) DATEPART(“yy” , GETDATE()) + “-” + RIGHT(“0” + (DT_STR, 2, 1252) DATEPART(“mm” , GETDATE()), 2) + “-” + RIGHT(“0” + (DT_STR, 2, 1252) DATEPART(“dd” , GETDATE()), 2) + “.txt”
If you click ‘Evaluate Expression’ you can see the name of the file with date embedded.