Get the LOG ( Text ) file name in SSIS Script task

Question

Get the LOG ( Text ) file name in SSIS Script task

Mohan.deval

Say Hey Kid

Points: 713
More actions
December 30, 2010 at 9:55 am

#232411

Hello,
I have configured Logging to generate the log files, filename example given below, using expressions
to create a Logging Text file.
Since this Log file is generated dynamically( based on time in milliseconds) , I have problems accessing the Name of the file in the SSIS Script TASK.
I would like to Mail this Log file ( or atleast Log file name with with full path to administrator in the last step in case SSIS package FAILS.
Is there a System variable name representing the name of LOG file that I Can Access in Script task ?
D:\SSIS_ExecutionLogs\Hummer_AutomatedCommissioning\Hummer_AutomatedCommissioning_2010-12-13_173342_Log
Expression to create a Logging Text file.
"D:\\SSIS_ExecutionLogs\\" + @[System::PackageName] + "\\" + @[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" +
".TXT"
Please Help....

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

Assign the "filename" to a DTS variable in a script task, then use the variable as the attachment and the path for the flat file destination.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Mohan.deval Say Hey Kid Points: 713 More actions · Answer 2

Wov Thanks for the Quick reply on a early release Day !!!.

Nope, The issue is :-

the Expression I provide earlier is evaluated BEFORE the Script task is even initiated. So the Milliseconds Time lapses till the time the Script task is taken for execution. So the LOG file name in the Script task & the Actual LOG File Name decided by the SSIS PACKAGE expression is evaluated differently & has Time lapse.

Did I understand you correctly or you meant it differently ?

Please help,

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

Yes, I got what you meant. What I mean is; you need to change the expression that generates the filename to a variable, then add a new script task that begins before the dataflow task where you are creating your log file. In the new script task, build the variable, something like this: -

Public Sub Main()

Dts.Variables("Variable").Value = "D:\SSIS_ExecutionLogs\" & Format(Now(), "yyyyMMddhhmmss") & "_Log.TXT"

Dts.TaskResult = Dts.Results.Success

End Sub

Then you can use the variable for the destination of the log file and the attachment to the send mail task.

Does that make sense?

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Mohan.deval Say Hey Kid Points: 713 More actions · Answer 4

Thanx again for Quick reply...

Hmmm..... Yes... your soln. makes sense Some what.....

Let me try this, I can treat it as a work around,

The reason I say so becoz... I would have to add a Burden of adding Script task just to generate or grab the LOG txt File name for every SSIS Package ( Around 139 SSIS Packages) ( When I meant the LOG file name, did you realize that I meant the SSIS PACKAGE generated LOG File and NOT the user generated ASCII Text file created by vb.net in Script task used for user created for Logging.

There should be some way to access the SSIS PACKAGE Generated File Name, the Package is Currently writing to.

( I am not sure if this will work... since SSIS Package Logging File Name Configuration is a design time, and not sure if it is at run time. The varaible in the Script task will be evaluated only after the

first Script task has completed , till then what will be the variable's initialised value ?

Let me try your soln.. till you read this & reply.. Thanx again...

See the Capture.JPG attachment.

Mohan.deval Say Hey Kid Points: 713 More actions · Answer 5

NOPE.....

The Soln.. Provided as above does not work for my requirement.

Let me spell out my requirement once again if it is mispleading..... Any MVP's ???

How to access the PACKAGE Generated Dynamically created LOG File ( at Run time ) The PACKAGE LOG File by using Package LOGGING feature ( SSIS Log Provider for Text Files ) has been created by run time evaluated expression.

I have around 150 SSIS Packages, would like to use expressions or with a burden a Script task for each of the packages to get the name of the LOG file, and email it to Admins, the Full Log File name Path & if possible ALL the contents of the LOG file IF the package FAILS with a Send Email Task for PACKAGE Failure.

sunil.rangu Grasshopper Points: 21 More actions · Answer 6

I am working on the same issue, it is not the problem with path or file name but I think when trying to attach email the log file is still being used by another process ( means log file is still loading with the data) when the file is in use we can not attach that file via email.

I have two logic's in my mind if I get solution with any one of them I will post the same.

Meanwhile any solutions on this issue will help a lot.

Thank you.