I am currently using the SSIS Logging feature in my SSIS package. Currently, I have defined a destination log file, and each time the package is executed the log file gets appended with that days log. Im trying to figure out how best to keep the log file name static (it gets emailed out, and my email client looks for a particular log file name) yet include only todays log information and append the rest of the log information to a history log file or something like that. Has anyone tried doing something similar, or have any ideas on how best this can be accomplished? Thanks

You can't do this from teh same package that produces the log file. You would need a seperate process (which can be another package if you wish) that runs to send the log file. Using SSIS you can rename the file to whatever you like prior to sending it. -Jamie

Ok, so the way I was thinking was to add a "File System Task" to rename the preexisting file with something like"LogFileXXXX.csv" where XXXX was going to be todays date time stamp or something. Can anyone help me figure out how to do this? The destinationconection in the file system task editor only pulls up static connection names, is there a way to use expressions to create this name and assign it to the destinationconnection? Or is there a completely better way to do it? Thanks

You can use a expression on the connection manageritselfto set the connectionstring property.

i havent had much luck figuring out how to use the expression builder. can you tell me how i would go about renaming a staticly named file to something like "abcXXX.txt" where XXX would be todays date using the expression builder. thanks

YoungEngineer wrote: i havent had much luck figuring out how to use the expression builder. can you tell me how i would go about renaming a staticly named file to something like "abcXXX.txt" where XXX would be todays date using the expression builder. thanks Dynamically set a logfile name (http://blogs.conchango.com/jamiethomson/archive/2006/10/05/SSIS-Nugget_3A00_-Dynamically-set-a-logfile-name.aspx) -Jamie

I followed the above example and what i tried to do was copy my static file into a dynamically created file with the datatime stamp. But, in the File System Task Editor, even tho I have IsDestinationPathVariable set to true, and the destinationVariable setup as in the above example, it keeps erroring out that the destination file doesnt exist! Im trying to make it create the file...would this not work?

Looking for an update on this blog. Young Engineer, if you have resolved this, please provide the answer so we can follow in your footsteps! Thanks.

YoungEngineer wrote: I followed the above example and what i tried to do was copy my static file into a dynamically created file with the datatime stamp. But, in the File System Task Editor, even tho I have IsDestinationPathVariable set to true, and the destinationVariable setup as in the above example, it keeps erroring out that the destination file doesnt exist! Im trying to make it create the file...would this not work?Do you have DelayValidation set to true on the File System Task? Also, make sure that the variable contains a valid path/file as the default value -- even if it will be dynamically changed. This will help in debugging.

I don't know how relevant this can be to your issue; but I have an example to rename/moved a file in one step using the File system task: http://rafael-salas.blogspot.com/2007/03/ssis-file-system-task-move-and-rename.html

That example from the blog aboveis basically what I had this whole time, however, at runtime, I get an error that the destination file does not exist. True the destination file pointed to by the target file destination variable does not exist because im hoping it will create it for me! im trying to copy a file that already exists into a dynamically created (by date time) file. Does anyone else get the same problem?

Are you sure you are using 'RENAME FILE' as the operation of the fyle system task? also check you are providing intial values to the variables. If you use rename file; the task won't expect the destination file to exist.

I log to the SQL Server Log Provider. That way all of the data is in a database. Then using a task, I can extract the most recent entries (or whatever I want) via an SQL query. The data can then be saved to a file and e-mailed. Or sent in the body of the message. I find that keeping the log in a database is much more flexible. Rob

Rafael, Yes I have chosen the "Rename file" operation in the file system task, and set the IsDestinationPathVariable to True and assigned User::Target as the DestinationVariable. In the expressions window I chose Destination and set the expression to in the expression builder to assign "file1.txt" to User::Target. I assigned the DelayValidation to true and still get the following error message when I execute. Nonfatal errors occured while saving the package: Error at Start: The connection "file1.txt" is not found. This error is thrown by the Connections collection when the specified connection element is not found. What could I be doing wrong? Thanks

I just wanted to verify that you are not trying to log to this file inside the same package. This is a seperate pacakge that is moving the log file for a completely different package, right?

yep this is a completely different package. In fact, I tried just renaming a text file that exists (nothing to do with the logging problem that I mentioned above) and I still get the error above. Strange! If the case is that the file has to exist before renaming then the whole purpose of the rename file operation in the file system task is purposeless.

Can you try just copying the file to see if that works?

i really want the copy function, but since that didnt work i tried the rename function..apparently all the functions want a connection manager connection to a destination file even if i set the IsDestinationVariable to true. Ive tried with a connection to a static file, and it seems to work, but for my case, it wont help because I have a dynamically named file based on date time. Does this actually work for other people, can i email someone my simple package to see where im going wrong, i doubt im doing anything different being that the control flow only has one task and one connection manager to a source file. thanks!

Sure - john period welch at mariner hyphen usa period com

Feel free to email me your package. My email is in my profile of this forum.

ok..so i think i figured out what i was doing differently to other people on here. im still not clear why my way didnt work. My way: I dragged a file system task, dbl click. in the DestinationVariable field, i chose new variable in the drop down. gave it a name and clicked ok. Then i clicked the expressions option on the left of the file system task, and then clicked the elipsis in the expressions field on the right. Chose the property as Destination, and in the expression field clicked the elipsis to bring up the expression builder. expanded variables, chose User:Targetfile and then built the expression below. and clicked ok. New way: I opened the variables property sheet and added a new variable TargetFile and set the expression there. And in the File system task, just chose that variable from the DestinationVariable dropdown. I found that the con with using the variable property sheet is that there was no expression builder...is there any way of getting this to build the expression for the variable? Also, im just trying to understand why my way is different to the way that works. Thanks!!

See this Article about implementing ssis logging various ways. http://pragmaticworks.com/Products/Business-Intelligence/BIxPress/ssis-logging-auditing-monitoring.aspx SSIS-Tutorials-FAQs | Convert DTS to SSIS | Document SSIS | SSIS Tasks | Real-time SSIS Monitoring