Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SSIS Nugget: Dynamically set a logfile name

Anyone who reads this blog regularly knows I am a big fan of using expressions to achieve results in SSIS. In this post I'm going to show a very simple example of using them.

If you are building SSIS packages then you will be (or you should be) using the log providers to keep a history of your package executions. I generally use the Log Provider for Text Files:

 

One thing I like to do is have a seperate logfile for each execution of a package. I also like to easily identify which package the logfile was created by. Well guess what, all of this can be achieved with expressions.

Take the File Connection Manager that is used to point at the location where SSIS will create the logfile. I can apply an expression to the ConnectionString property in order to achieve what I want. Here is the expression:

"C:\\temp\\" +  @[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"

The bit that people have trouble with here is getting the date and time in the required format [YYYYMMDDHH24MISS] that will ensure the files appear in the order that they are created. Hopefully my putting this expression here will make it easier for people. In the screenshot below you can see the expression and what it is evaluating to:

 

Just to prove the point, here's a screenshot of the c:\temp directory after a few executions:

 

Hope that's useful to someone. In all probability the most useful thing is the expression to return today's date as a string. Of course, this technique can also be used for other files, not just log files.

The demo package can be downloaded from here.

-Jamie

 

Published Thursday, October 05, 2006 5:44 AM by jamie.thomson
Attachment(s): DynamicLogfile.zip

Comments

 

SSIS Junkie said:

Someone recently left a comment on my blog about Package Template Locations asking if I could share my

March 12, 2007 9:45 AM
 

Professional Association for SQL Server (PASS) SIG said:

April 4, 2007 3:00 PM
 

Michael Pegurri said:

Good afternoon Jamie!

Well done. I thank you for this reference.  I am following up on a blog on MSDN, referenced in the URL  

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1395556&SiteID=1

MSDN is down so I am following up directly. Hopefully "Young Engineer" is checking in.  I am doing much the same as he is, only I need to dynamically name a text file that I am generating out of SSIS. So I name the file XXXX.PO.123  where 123 is the day of the year (Julian? - sort of).  This is what I have entered based on your code above, but keep receiving the UNICODE errors that plague SSIS

"C:\XXXX.PO." + [DT_STR,3,1252]DATEDIFF("dd", [DT_STR,10,1252]DATEDIFF("dd", @[System::StartTime] ,("12/31/"+ (DT_STR,4,1252)DATEPART( "yy" , @[System::StartTime]   ))),  365)

ERROR: the function datediff does not suport the data type DT_WSTR for parameter 3.  The type of parameter could not be explicitly case into a compatible type for the function.  to perform this operation, the operand needs to be explicity caset with a cast operator.  error code 0xC0047089

can you see the error of my ways?

April 12, 2007 8:49 PM
 

jamie.thomson said:

Hi Michael,

First of all you have nested DATEDIFF functions which simply doesn't make any sense.

I find it better to break expressions over several lines to help understand them.

Basically though, the error is exactly what the message says. The third parameter of the DATEDIFF function doesn't accept strings, it accepts a date. As the documentation says: http://msdn2.microsoft.com/en-us/library/ms139927.aspx

I don't know what you mean by unicode errors plaging SSIS. In this case your expression is wrong - you can't blame SSIS for that. Perhaps better to say errors plague your packages :)

-Jamie

-Jamie

April 12, 2007 9:40 PM
 

Saru said:

Hi,

Is there a way to create Folders also dynamically for the Logs, just like you did for files?

May 9, 2007 3:43 PM
 

jamie.thomson said:

Hello Saru,

I use WIX (wix.sourceforge.net) to deploy my packages and all associated config files. usig WIX you can also create folder structures.

-Jamie

May 9, 2007 4:14 PM
 

Alfonso said:

Can you get the location that the package is running from dynamically at runtime?

Ex. Instead of "C:\\temp\\" could I use some something like

@[System::PackageDir] + "\\Logs\\"

I guess this may be more of an available variables question, but I haven't found one that looks like what I want.  Perhaps this information is only available from a script through the object model and not in an expression.

Thanks,

Alfonso

PS I have found your postings VERY helpful and tried to join conchango, but I am still waiting on the confirmation email.

June 6, 2007 7:54 PM
 

jamie.thomson said:

Alfonso,

No, its not possible to do that. The reason being that it might not actually be running in a directory e.g. If you execute from SQL Server.

Which confirmation email are you waiting on? If you want to submit your CV then email it to me jamie.thomson@removethisbit.conchango.com

-Jamie

June 6, 2007 8:39 PM
 

ultimatealf said:

Thanks for the quick feedback.  That makes a lot of sense and saves me a lot of time chasing down a feature that doesn't exist.

June 6, 2007 8:58 PM
 

Subhash said:

Jamie,

  In your expression, you store the log files in a local folder. But If I want to store the log files in a shared folder, What modifications are reqd in this expression

June 26, 2007 12:31 PM
 

Ravi said:

Hi,

I am using this dynamic log file name. However I have one issue. How do I send this file using Send Mail Task? It error out saying the file does not exist.

Thanks,

June 28, 2007 12:57 AM
 

Chas said:

Jamie,

Thanks, your article detailing the Log Provider for Text Files is great. I am now using them extensively throughout my projects.

Just one question though, is there a utility to read and format the resultant log files? I am opening them in a spreadsheet, which is better than plain text, but not what I am really looking for.

Thanks.

July 10, 2007 11:02 AM
 

jamie.thomson said:

Chas,

There is no utility that I know of. Perhaps you could use a SSIS package to dump them into a table :)

Actually, you could probably build a SSRS report on top of them - assuming SSRS can display the content of text files.

-Jamie

July 10, 2007 4:14 PM
 

jt said:

Hi Jamie,

I'm using your expression to set the connection string on the log file.

The problem is I get two log files each time I execute the package, e.g.

ApplySQLfromFolder20070814185413.log

ApplySQLfromFolder20070814185420.log

Any ideas?

August 14, 2007 6:57 PM
 

jt said:

Hii Jamie,

please ignore the last comment about two log files, it doesn't happen when you execute the package normally, i.e. outside of the designer.

thx

jt

August 15, 2007 9:56 AM
 

Lebs said:

Hi JamiHi,

Is there a way to set a checkpoint path dynamically? I have disabled SSIS package configurations , and I would like to retrieve the checkpoint  path from a database that stores the variables. Problem is that the package always saves the checkpoint file to the design defined path not the retrieved path.

Thanks,

November 15, 2007 4:22 PM
 

jamie.thomson said:

Hi,

You can set an expression on CheckpointFileName property of the package. Thus defining it dynamically.

-Jamie

November 15, 2007 6:52 PM
 

Lebs said:

Hi,

My apologies, I didnt explain properly.

I am setting an expression on the CheckpointFilename dynamically. But it still saves the file to the wrong place.

Thanks,

November 16, 2007 7:50 AM
 

jamie.thomson said:

November 16, 2007 10:13 PM
 

Christphe said:

Thank you !!!   Just wat I was searching for;

January 15, 2008 3:34 PM
 

Justin B said:

Ravi, I'm running into the same issue.  By the time I try and email the file, the System::StartTime has incremented, and therefore can't send.  ALSO, if it happens to finish within that split second, I still can't email the attachment because the Log provider still controls access into it.  I'm anxious to hear of any solutions that would allow us to email off the dynamic log files as an attachment.

I've also tried setting the logfile name in a variable, then using that variable as the ConnectionString property in expressions,  unfortunately, the variable doesn't get set early enough for the log provider to use it.  I've tried setting this variable many ways (OnPreExecute at the package scope, OnPreValidate, as a Script Task attached to a sequence container, and setting OnError to be logged just on the sequence container).  No success any of these ways.

February 28, 2008 7:25 PM
 

jamie.thomson said:

Justin B,

I assume you're emailing the file from a different package, is that right?

if so, you're going to have to pass the logfile name from one package to another. There are a few options so let me know exactly what your setup is and I *might* be able to help.

-jamie

February 28, 2008 7:42 PM
 

Justin B said:

Actually, I'm emailing from the same package.  I finally got it to work!  First I created two variables on the package level: ErrorLogFolder, and ErrorLogFileName.  

Next, I created a sequence container that held all of the tasks that I'm running.  Outside of the sequence container, I created a script task, and connected it to the sequence container for "Failure".  The script task grabs the last created log file from the ErrorLogFolder, and copies it to a temporary file.  The entire script code is located here (http://pastebin.com/f602da992).  Next, I attached an Email task component to the Script task, and set the File Attachment property as an expression to the variable ErrorLogFileName.  Finally, I attached a File System Task to DELETE the file using the ErrorLogFileName as the Source Path, (set source path as variable to true).

February 28, 2008 9:20 PM
 

Justin B said:

I forgot to only pull files with that package name in the file name.  Use this script instead (http://pastebin.com/m60892db3).  Thanks for the great blog on logging Jamie.

February 28, 2008 10:51 PM
 

Tim Toennies said:

Jamie,

Is it possible to store the values of my variables using the SSIS Logging framework?  If that's not possible then do you have any recommendations?  I was thinking about using a Script Task on the OnError Event Handler to write a file with these values but that feels a bit rudimentary.  

Any suggestions?

March 20, 2008 10:27 PM
 

jamie.thomson said:

Tim,

Yeah, use a script task to call:

Dts.Events.FireInformation

and put the value of the variable into the appropriate parameter. It will fire an information event which will automatically end up in your log.

-Jamie

March 20, 2008 10:36 PM
 

Paul O said:

Hi Jamie,

I've got a ForEachLoop Enumerator that takes a list of tables and performs some sequential analysis on columns contained in them. The results are written out to a .csv file for each table (the files are dynamically named using variables and BLOCKED EXPRESSION using streamwriter. Downstream in the dataflow I then want to email these files to a user before returning to the beginning of the loop (i.e. processing the next table).

Everything works fine except for the attachment of the files. I've tried both the send mail task and Execute SQL (sp_send_dbmail), with DelayValidation set to true on both the sequence container and the mail task container. No joy in either case - the SQL task fails with "Executing API 'CreateFile' failed with error number 32", and the send mail task fails similarly with a file connection error. If I disable the mail send and use the package to generate the output files, then disable the dataflow and use the package just to send the (already present) files, it works ok - so security seems to be ruled out. It looks as though it's a validation issue but I was hoping DelayValidation would solve that....

Any ideas?! It's driving me nuts!

If possible I'd like to avoid using a dummy file to force the validation, though obviously that's not-so-secret option (c).

Cheers!

Paul

May 19, 2008 1:52 PM
 

jamie.thomson said:

Paul,

Errrr.....pass. Sorry, I don't know.

You could try a different option for email though: http://blogs.conchango.com/jamiethomson/archive/2006/07/03/SSIS_3A00_-Sending-SMTP-mail-from-the-Script-Task.aspx

Who knows. It might help.

-Jamie

May 19, 2008 1:56 PM
 

Paul O said:

Cheers Jamie, I've tried that just now...

...it still doesn't work but the error message is much more detailed - the script sees the files as still being used by another process (i.e. the dataflow that creates them). Is there any means of 'unlocking' a file that anybody knows of?

Apologies if I'm missing something obvious - VB .Net is not really my arena.

Ta

Paul

May 19, 2008 4:16 PM
 

Paul O said:

Ok, StreamWriter.Close() as a PostExecute routine in the script trans used to create the files upstream has done the trick.

It's a constant challenge to move out of the DBA zone and into the realm of VB axioms!

May 19, 2008 6:57 PM
 

Adnan said:

Hi Jamie,

I am trying to create a log file that gets its contents from a stored procedure containing a number of Print Statements in T-SQL.  I am doing this because the stored procedure returns lots of info foreign to the SSIS environment.

Is there any way to dump the print statement contents into a flat file in SSIS?  If not, what other method can you suggest?

Thanks a bunch,

Adnan

June 10, 2008 4:01 PM
 

ajaz said:

I want to execute a SSIS package from within my VB.net code. I want to provide the source file name (flat file) dynamically. How can i do that?

Thanks

October 15, 2008 3:00 PM
 

Hemant said:

Thanks for sharing above information. Your blogs always help me.

I have one question- I want to create log file only when data is available means i have selected OnError event only to be added into log file and i want to generate log file only when data is available(i.e.any error occured). How can i do that.

November 6, 2008 4:37 AM
 

jamie.thomson said:

Hemant,

I'm afraid there's no way to do that currently. Good idea tho - try asking for it at http://connect.microsoft.com/sqlserver/feedback

-Jamie

November 6, 2008 9:53 AM
New Comments to this post are disabled

This Blog

Syndication

Powered by Community Server (Personal Edition), by Telligent Systems