Setting up my OLE DB connection is fine, but when I add the Excel file destination, I cant select the worksheet name yet because the excel file doesnt exist. It wont exist until the initial script flow task has executed. You need to create a dummy Excel file first, so that you can configure your dataflow. After that you need to create an expression on your Excel connection manager to point to the dynamically created Excel file. Therefore you need to store the full filepath to the excel file in a variable inside the script task. You can use this variable in your expression. Set the DelayValidation property of your dataflow to TRUE, otherwise the package will fail validation.MCTS, MCITP - Please mark posts as answered where appropriate. Answer #1: Have you tried turning it off and on again? Answer #2: It depends...

Hi MustafaH Your link is perfet and I like it, some minor issues it has that needs to be pointed out 1- you need admin rights 2- you have to Set "Ad Hoc Distributed Queries'" which most DBA won't allow you to do But the code is verygood and dynamic I feel that they are simple code like http://www.rafael-salas.com/2008/03/ssis-and-dynamic-excel-destinations_01.html that can be used to create EXCEL SHEETS , but still in this case "Ded_innit" is dealling with 2 fixed tables, so i think thebest way is to have a EXCEL sample file with 2 sheets with fix names and he can avoid the CREATING sheets partSincerely SH -- MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Here is the situation. I have an Excel Template that has the column headings and formatting of 2 tables in my SQL server. There is no data in this template. I want to create a package that: copies the template file and renames it according to the date e.g. Copy Test_Template.xlsx to Test_21_02_2012.xlsxCopy the data from the first table into the first worksheet in the newly copied and renamed excel fileCoup the data from the second table into the second worksheet in the same excel file as above. Its only 3 simple steps but I am not really getting anywhere. I have started off with a script task that copies and renames my template file according to the date. The script task is connected to the next stage - the data flow task. Setting up my OLE DB connection is fine, but when I add the Excel file destination, I cant select the worksheet name yet because the excel file doesnt exist. It wont exist until the initial script flow task has executed. I think I may be going about this the whole wrong way so could somebody point me in the right direction. Much appreciated.You can't dangle the bogus carrot of possible reconciliation in front of me whilst riding some other donkey

Setting up my OLE DB connection is fine, but when I add the Excel file destination, I cant select the worksheet name yet because the excel file doesnt exist. It wont exist until the initial script flow task has executed. You need to create a dummy Excel file first, so that you can configure your dataflow. After that you need to create an expression on your Excel connection manager to point to the dynamically created Excel file. Therefore you need to store the full filepath to the excel file in a variable inside the script task. You can use this variable in your expression. Set the DelayValidation property of your dataflow to TRUE, otherwise the package will fail validation.MCTS, MCITP - Please mark posts as answered where appropriate. Answer #1: Have you tried turning it off and on again? Answer #2: It depends...

Cheers guys - I got there in the end!You can't dangle the bogus carrot of possible reconciliation in front of me whilst riding some other donkey

1 - Make a sample excel file that has 2 sheets and name it Sample.xlsx, the sheet name will have to be fixed, try to use the same name as the tables used in step 2 and 3 as you had mentioned 2- make a folder SAMPLE and copy the file in that folder 3- by using a SSIS variable you must have the file and path of the excel sample file 4-use another variable (uVar_Rename) that will copy the sample file and rename it to what ever you want 5-use a script task and the variable uVar_Rename to make the new path and file 6- use a FSYS to copy the sample file and rename it use operation "RENAME FILE" 7- use a SSIS variable for the Excel "Connection string , go to excel propety page and see expressions and hook up the variable to the "connection string" 8- use the script task to make the connection string variable to point to the new renamed file 9- use a DFT for your first table to the first exel sheet, 10- use a second DFT for your second table to the second exel sheet 11- remeber that you hav eto do the sheets one by one , because some cases they kind of hang good luckSincerely SH -- MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Hi, Have a look on this link it should resolve your problem http://www.codeproject.com/Articles/301542/Creating-Excel-File-and-ExcelSheets-dynamically-fr