posted 11/8/2010 by timmurphy - Views: [3104]
It is handy to be able to look at a long list of Package Log Files and find the one you are looking for by a time stamp. Here’s how to append a Timestamp to a log file using package name.
In this case I have already created a txt log file connection named DimTransDesc which writes to c:\SSISLogs. My business requirements are very specific for the format I must use.
In the Connection Manager, click on the log file connection and press F4 to bring up its properties.
Click the + symbol and then the expression symbol to open the editor.
Select Connection String and open the editor.
Insert the expression to append the Timestamp to the package name. You can copy and paste this expression or change it to match your own business requirements.
"C:\\SSISLogs\\" + @[System::PackageName] + " Log "+ (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() ) ) + "m.txt"
Click on Evaluate Expression to make sure it works.
If the Evaluated value is what you want, click OK and OK again to close the editor.
When you run the package you should be able to navigate to the appropriate directory and find your log file with the Timestamp appended to the name.
This should get you started.
Very useful tip. Thanks for sharing this.
Very nice info Tim - thanks!
I agree with the others. That is a good tip Tim.
I have never had to do this. But, I have always thought that eventually I would need something exactly like this. Great tip.
Excellant Tip, Thanks