How To Create A Dynamic Log File In SSIS

In SSIS you can create a log file to track the behavior of the SSIS package execution. These are the steps used when creating a dynamic log file

• Create a log file in connection manager.

• Right click on connection manager ->New File Connection->give the proper sources to the log file.(Usage type- ,Existing file/New file Path- path of the log file)

• Or go to SSIS -Login (in the menu bar) and create a new File Connection

• Login Configurations

• In the menu bar Click SSIS -> Logging.

• Click checkbox for the Package(left side).

• Select the configuration and give a name for the log file.

• Click the Details tab to fire the events.

• Making the log file dynamic

• In the properties tab of the log file connection (click on log file in the connection manager and view the properties tab) Click on expression and give the following settings

• Property – Connection String

• Expression - @[User::gvErrorLogDir] + @[System::PackageName] + " "+ (DT_WSTR, 4) YEAR( GETDATE() ) + "-"+ ((LEN((DT_WSTR, 2) MONTH( GETDATE() ) )>1)?(DT_WSTR, 2) MONTH( GETDATE() ):"0" + (DT_WSTR, 2) MONTH( GETDATE() ) )+"-"+((LEN((DT_WSTR, 2) DAY( GETDATE() ) )>1)?(DT_WSTR, 2) DAY( GETDATE() ): "0"+ (DT_WSTR, 2) DAY( GETDATE() ) ) + " " + ((LEN((DT_WSTR, 2) DATEPART("Hh", GETDATE() ) )>1)?(DT_WSTR, 2) DATEPART( "Hh", GETDATE() ) : "0" +(DT_WSTR, 2) DATEPART( "Hh", GETDATE() ) ) + "h-"+ ((LEN((DT_WSTR, 2)DATEPART( "mi", GETDATE() ) )>1)?(DT_WSTR, 2) DATEPART( "mi", GETDATE() ) :"0" + (DT_WSTR, 2) DATEPART( "mi", GETDATE() ) ) + "File.txt"
  

• 
  

Relevant Links
How To Create A Logger In SSIS
SSIS Junkie
Integration Services Log Providers-MSDN