blogs.conchango.com

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

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?

Hi,

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

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.

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.

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

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,

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.

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?

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

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,

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,

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

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.

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).

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.

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?

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

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

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!

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

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

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.