Hi All, I created a package which runs everydays and dumps the data into an excel file. The problem iam facing is that -today the package runs and fills in the excel file,tomorrow it again runs and fills in the data without deleting the previous records....... But i want it to delete the records already present and fill in the excel only with the new records... Any help is greatly appreciated. Thanks in Advance, SVGP

Can you simplyinsert into a new file everyday that is datetime stamped then overwrite the apropriate one through a script task? If you keep the paths in variable names you can chose to delete or keep the timestamped files. This may require you to delay validation, but I believe I've found ways around that though. Or you could delete and re-create the file everyday. You may also have to tweak the package validation.

You can create the excel file on every execution. You can create a different file each time by adding a unique identifier to the file name; or you can use the same name every time; but then you have to have an additional step to delete/move the existing file See if this post helps you: http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html

I have found Excel destinations a bit tricky to work with. I had a similar issue wherein each day I wanted to created a different file with the previous day's date appended to the file name. When I tried to execute the package however it would give me an error stating that the file did not exist. I have assumed that for Excel destinations SSIS requires the file to already exist prior to execution. Anyway, this is how I solved the issue. First, I realized that Flat File destinations are a little more forgiving and a bit more flexible. I therefore chose to output the data in a comma-delimited format csv file. Because csv files open well in Excel I did not have any problem. The other advantage with Flat File destinations is that you can overwrite them. If using a Flat File is not an option what you can do is to have a "template" Excel file that is always empty and has a different name than the one being populated by your package. Once your package is done populating the target use the File System Task to make a copy of the target file, renaming in the process. Use another File System Task to then copy the template file to the target file area and overwrite the target. You now have a fresh empty target to start with and your previous target has already been saved. I hope this makes sense.

OK, I just realiazed why I had to abandon the Excel method. Even when the SSIS package was presented with a new empty target it remembers how many rows were inserted from the previous run and consequently begins a fresh "append" at the n+1 th row in the new spreadsheet. In other words, if SSIS had inserted 50 records the previous day it begins the next day's insert at the 51st record even if the target is empty. I wonder if there is a buffer that SSIS maintains and if there is a way to clear it. I am now using a Flat File destination and it is working great. It is a really good alternative to using Excel destinations.

SSIS 'does not remember' what was the last row number it inserted. Perhaps the way you removed the data from the existing file made SSIS to still 'see' those rows as been used. If you use the technique I described in my blog, to create the excel file you won't have that problem.

Hi Rafael, I tried the way you mentioned in the blog but Iam getting the following error,even after trying a lot iam unable to resolve this Expression cannot be evaluated The variable "user::excelFilename" was not found in the variable collection.The variable might not exist in the correct scope. Attempt to parse the expression "@[user::excelFilename]" failed and returned error code 0xC00470A6.The expression cannot be parsed.It might contain invalid elements or it might not be well-formed.There may also be an out of memory error. i tried both with package scope and task scope both didnt work as the same error was coming. Did you face the same issue when you followed this method? Any help on this is greatly aprreciated. Thanks, SVGP.

Please provide the expression you are using and the property name where are you trying to apply it to.

Hi I created a global variable "filename" and then in the property window of the variable i changed the evaluate as expression to 'true' then i wrote an expression in the same properties window like "H:\\Sharedrive\\Reports\\NAR1+select CONVERT(Char(10),getdate(),101)+.xls" then i took the connection manager properties and in the expression pane i gave excel file path and in the expression builder i gave @[user::filename] and iam getting the error that i wrote previously. Please let me know if what iam doing is wrong. Thanks, SVGP

That expression even when is valid; it evaluate to something like: H:\Sharedrive\Reports\NAR1+select CONVERT(Char(10),getdate(),101)+.xls Which is not a valid path/file name. You need to work out the expression untill you get the desired date format.

Thsi expression should work for you: "H:\\Sharedrive\\Reports\\NAR1" + RIGHT("0" + (DT_WSTR,2)DAY(getdate()),2) + RIGHT("0" + (DT_WSTR,2)MONTH(GetDAte()),2) + RIGHT("0" + (DT_WSTR,4)YEAR(GetDAte()),4) + ".xls"

Hi Rafael, Thanks a lot for the code. Your code is working but again iam getting this error. ' H:\sharedrive\Reports\NAR106092007.xls' is not a valid path.Make sure that the pathname is spelled correctly and that you are connected to the server on which the file resides. Thanks, SVGP

Hi Rafael, Kindly let me know if you come to know the cause of this error. Thanks, SVGP.

I do not think you can use the drive letter - you need to specify the name of the server with double backslashes - so you need \\\\server_name within quotes so that it will translate to \\server_name.

Swan, the message is very explanatory. Is this path valid? H:\sharedrive\Reports\ or should it be: H:\sharedrive\Reports\NAR\ If the right one is the second option; then you need to modify the expression to add an extra '\' Also make sure that the user executing the package (are you using BIDS?) has access to that directory and it can create files.

Hi Rafael, The path i gave was wrong,when i fixed it,its working. Thanks for all the information you provided.It was very valuable to me. Thanks a lot Regards, SVGP

Is it true that SSIS does not allow you to specify that an excel destination step should clear previous data and replace it with the new values? If so this is quite disappointing. They offer that option in thier Mickey Mouse "Microsoft Query" product but not in their enterprise etl tool? Please tell me I'm wrong and just cannot find the right check box in SSIS.

Rafael, http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.htmlis not working. is it possible for you to post it here?Thanks.

If Rafael's post isn't working you can try this solutionhttp://samuelhaddad.com/2009/03/31/overwriting-an-excel-file-destination-using-ssis/it is short and to the point. I think it is very clear.

I'm using SSIS 2005 and i had a requirement to export some data in to an excel file. The data transformation 'excel destination' task does not have a overwrite property defined and the issue is not able to overwrite the existing file and it keeps appending the data in to the existing file (when the job runs daily). The solution described in the above links did not work for me as there were exceptions " drop table on excel is not supported by ISAM Engine etc..". Again, i do not want to have a flat file destination either. Here is what i did as a workaround, Before you execute the data flow task (for example), have a template excel file and create a 'File System Task' that does nothing but copies the file (with the format, if you happen to have any columns defined in the excel file) and make sure the destination overwrite property set to 'true'. In other words, add a file system task before the whole thing and make sure it does nothing but copying a template of the excel file you want to have by overwriting the existing file with data. Hope this helps.Srikanth

http://samuelhaddad.com/2009/03/31/overwriting-an-excel-file-destination-using-ssis/worked fine for me and I am on SSIS 2008, do not have 2005 available to try.

Here is what i did as a workaround,Before you execute the data flow task (for example), have a template excel file and create a 'File System Task' that does nothing but copies the file (with the format, if you happen to have any columns defined in the excel file) and make sure the destination overwrite property set to 'true'.In other words, add a file system task before the whole thing and make sure it does nothing but copying a template of the excel file you want to have by overwriting the existing file with data. After much frustration with this issue, I too have settled on the "template file" method. I use BIDS to create the file initially (with the correct column names). After the file has been created for the first time, I open it, select all rows but the first one, delete them (from the Edit menu, not using the delete button), save it and copy it to a Template folder. This creates an empty "table" (worksheet) with the correct column headings. In the SSIS package, I have a File System Task the copies the template file over the target file before executing the Data Flow tasks.Another alternative, suggested by others, is to use SQL statements to DROP the "table" and recreate it using CREATE TABLE in Control Flow, beforeexecutingthe Data Flow tasks. In this case, you could copy the CREATE scriptgenerated byBIDS while initially creating the "table" to save some effort and get it right first time. Another possibility could be to use "DELETE FROM table". Both options depend on what is allowed (or not allowed)by the Excel driver.Another probelm that can arise with Excel filesrelates to data types. The most common issue is the Excel columns have a maximum of width 255 characters. If you have source data with, say, VarChar() columns greater than 255 characters, validaion will trip you up. This could be another case for exporting to CSV sometimes. Of course te "proper" solution is to resolve any such issues with the Data Transform Task (for example) before the data is presented to Excel.Using a Flat File (CSV)Desination is a way to avoid both issues. The main drawback is that you create a file that can easily be imported into Excel, not an actual Excel file.I assume that the problem arrises because SSIS wants to validate the destination file prior to execution so that the file must exist and have the correct worksheet with the correct column names.Things would be much simpler if the Excel File Destination had options to overwrite or append the existing file. Perhaps the options would have to be at a "table" (worksheet) level for those who want to manipulate multple "tables" in a single Excel file.

SSIS 'does not remember' what was the last row number it inserted. Perhaps the way you removed the data from the existing file made SSIS to still 'see' those rows as been used. If you use the technique I described in my blog, to create the excel file you won't have that problem. To explain further my message below, if you selectdata rowsin Excel then press the Delete button, the data is deleted but not the rows, The Delete menu item on the Edit (I think) Menu deletes the rows.

Your suggestion was helpful. Thanks, Rafael.

I wanted to leave one concise answer for the thread even though may be a bit old now... Simply use a Flat File connection manager, and check the "Overwrite data in the file" option (located on the Flat File Destination component Connection Manager options right under the connection manager name in the editor) Regards J Oliphant, Data Architect Jeff Oliphant