Is it possible that i can create a dynamic excel file (destination) ex, i want to create a Dyanamic Excel destination file with a filename base on the date this will run on jobs. Is this possible? 11172006.xls, 11182006.xls

Sure. With just about any destination, including Excel, the name/location can be dynamic.1. Create a string variable which represents the excel file name, set the variable's EvaluateAsExpression property to true, and set the expression to something dynamic, for example: "ExcelTarget" + (DT_WSTR,4)DATEPART("yyyy",GETDATE()) + ".xls"2. For your excel connection manager, in the expressions node of the Properties tab, set the connection string property to the variable you just created. That's it.You can skip step I and write the dynamic file name expression directly as in step 2. However, the advantage of a variable is that you can easily view it by setting breakpoints, and looking at the dynamic value in the Locals or Watch windows. If you could evaluate expressions in the immediate window, there would be less need for the variable to contain the filename.

Hi Thanks anyway I'm gonna test it, if it's going to work, I hope it does. I'll reply again after i check it out Anyway thanks, hope this work

Jaegd, Not sure if that will work. I am working on a similar problem now. I am trying to load the contents of a table into an Excel file every week with a datestamp in the filename. I've tried a few approaches but haven't found a good solution yet. But here's what I found so far. 1. The first approach was to dynamically configure the connection string or filename property of the excel connection to generate a unique name every week. In design time, you will have no problem creating the first file, but at runtime, the package fails in validation as the file doesn't exist. I tried delaying validation but it only delays the inevitable. The conculsion I came to is that, changing the filenames using expressions will only help you point to a different XL file thats already created but doesnt help you create a new one on the fly. Jamie, Kirk or someone please comment on this. 2. The second approachis to have a target with a static name like "TargetExcelFile.xls", which already exists, load data into this file and use a file system task to make a copy of it with the appropriate filename, which isconfigured with a variable or an expression. That seemed to work but there is no way of truncating this excel file before loading every week. The data just keeps appending. I was unable to use a truncate or delete command on the XL connection. One approach I am trying right now is to create the xl file by issueing an explicit create table command and then load data. I hope it works. Thanks....

Ravi G wrote: Jaegd, Not sure if that will work. I am working on a similar problem now. I am trying to load the contents of a table into an Excel file every week with a datestamp in the filename. I've tried a few approaches but haven't found a good solution yet. But here's what I found so far. 1. The first approach was to dynamically configure the connection string or filename property of the excel connection to generate a unique name every week. In design time, you will have no problem creating the first file, but at runtime, the package fails in validation as the file doesn't exist. I tried delaying validation but it only delays the inevitable. The conculsion I came to is that, changing the filenames using expressions will only help you point to a different XL file thats already created but doesnt help you create a new one on the fly. Jamie, Kirk or someone please comment on this. 2. The second approachis to have a target with a static name like "TargetExcelFile.xls", which already exists, load data into this file and use a file system task to make a copy of it with the appropriate filename, which isconfigured with a variable or an expression. That seemed to work but there is no way of truncating this excel file before loading every week. The data just keeps appending. I was unable to use a truncate or delete command on the XL connection. One approach I am trying right now is to create the xl file by issueing an explicit create table command and then load data. I hope it works. Thanks.... My suggestion would be to tweak a bit your 2nd approach: You may have, perhaps,an empty file with the required structure, let's say TargetExcelFile.xls that you copy/rename to the excel destination component's expected location prior to the dataflow. For that, you could use a file system task that usesan expression to rename the file with the right name every time. Then in the data flow the excel connection string should use the same expression to find the just renamed file.

Ravi, I did indeed forget a step. Before the dataflow which writes to the dynamic excel target file, add in a Execute SQL task against the Excel connection manager to create the table (aka worksheet). This is what you suggested at the very end and it does work.For example,CREATE TABLE `Excel Destination` ( `GeneratedInt_1` INTEGER)Then create the connection string variable on the connection manager as follows:"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\temp\\" + "ExcelTarget" + (DT_WSTR,4)DATEPART("yyyy",GETDATE()) + ".xls" + ";Extended Properties=\"EXCEL 8.0;HDR=YES\";"And yes, as you were intimating, the delay validation on the dataflow should be set.

Jaegd, I was just about the post the same thing and you beat me to it. I tried my third approach and it works exactly the way I wanted. By the way, youcan set the filename property dynamically instead of the connection string property, its simpler and more readable.

Hi, I'm kinda new here in SSIS, is it possible that you can help me to do this step by step, I'm kinda lost Hope you can help me this one THanks jaegd wrote:Ravi, I did indeed forget a step. Before the dataflow which writes to the dynamic excel target file, add in a Execute SQL task against the Excel connection manager to create the table (aka worksheet). This is what you suggested at the very end and it does work.For example,CREATE TABLE `Excel Destination` ( `GeneratedInt_1` INTEGER)Then create the connection string variable on the connection manager as follows:"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\temp\\" + "ExcelTarget" + (DT_WSTR,4)DATEPART("yyyy",GETDATE()) + ".xls" + ";Extended Properties=\"EXCEL 8.0;HDR=YES\";"And yes, as you were intimating, the delay validation on the dataflow should be set.

Sure. I was planning to post a summary of my findings anyway. I'll be posting it soon.

This example is useful for loading data from an OLEDB source into a dynamically created Excel file. NOTE:Thisis the core functionality. Things like logging, checkpointing, documentation, etc., are at the user's discretion. Steps:1. Click on package properties. Set "DelayValidation" property to True.The package will not validate tasks, connections, until they are executed. 2. Create a package level variable "XLFileRootDir" as string and set it to the rootdirectory where you want the excel file to be created.Example: C:\\Project\Data\ 3. Create an Excel connection in the connection manager. Browse to the target directoryand select the destination XL filename or type it in. It doesn't matter if the file doesn't exist. 4. Go to the Excel connection properties and expand the expressions ellipse (The buttonwith "..." on it).Under the property drop down, select 'ExcelFilePath' and click on the ellipse to configure the expression:@[User::XLFileRootDir] + (DT_WSTR, 2) DATEPART("DD", GETDATE()) + (DT_WSTR, 2) DATEPART("MM", GETDATE()) + (DT_WSTR, 4) DATEPART("YYYY", GETDATE()) +".xls"This should create an xl file like 01132007.xls. 5. Add a SQL task to package and double click to edit.In the general tab, set 'ConnectionType' to 'Excel'.For 'SQLStatement', enter the create table SQL to create destination table.For example:CREATE TABLE `Employee List` ( `EmployeeId` INTEGER, `EmployeeName` NVARCHAR(20))Copy the create table command. It will come in handy later. 6. Add a Data Flow task. In the data flow editor, add an OLEDB source and an Excel destination.Configure the source to select EmployeeId and EmployeeName from a table. 7. Connect this to Excel destination. In the destination editor, select the Excel connection in themanager, choose 'table or view' for data access mode and for 'name of the Excel sheet' click on new button and paste the create table command from Step 5.Map the columns appropriately in the mappings tab and you are done. Let me know if you have any questions.

Hi Ravi G and to other's who answer thanks to all anyway does anyone here know's how to generate a guid? and use it as a file name? do i need the script task? lastly i hope this is not to much to ask, does anyone here know's how to connect to Active directory? the basic concept at least? anyway thanks to all you guys!!! cheers

Hi, Ravi G I successfully created the excel file but i still have one more problem, how would i dynamically map data from it after i created the excel file(I already have the filed and the table)? since the created excel file was the the destination file. Hope you can still help me on this one Thanks Ravi G wrote: This example is useful for loading data from an OLEDB source into a dynamically created Excel file. NOTE:Thisis the core functionality. Things like logging, checkpointing, documentation, etc., are at the user's discretion. Steps:1. Click on package properties. Set "DelayValidation" property to True.The package will not validate tasks, connections, until they are executed. 2. Create a package level variable "XLFileRootDir" as string and set it to the rootdirectory where you want the excel file to be created.Example: C:\\Project\Data\ 3. Create an Excel connection in the connection manager. Browse to the target directoryand select the destination XL filename or type it in. It doesn't matter if the file doesn't exist. 4. Go to the Excel connection properties and expand the expressions ellipse (The buttonwith "..." on it).Under the property drop down, select 'ExcelFilePath' and click on the ellipse to configure the expression:@[User::XLFileRootDir] + (DT_WSTR, 2) DATEPART("DD", GETDATE()) + (DT_WSTR, 2) DATEPART("MM", GETDATE()) + (DT_WSTR, 4) DATEPART("YYYY", GETDATE()) +".xls"This should create an xl file like 01132007.xls. 5. Add a SQL task to package and double click to edit.In the general tab, set 'ConnectionType' to 'Excel'.For 'SQLStatement', enter the create table SQL to create destination table.For example:CREATE TABLE `Employee List` ( `EmployeeId` INTEGER, `EmployeeName` NVARCHAR(20))Copy the create table command. It will come in handy later. 6. Add a Data Flow task. In the data flow editor, add an OLEDB source and an Excel destination.Configure the source to select EmployeeId and EmployeeName from a table. 7. Connect this to Excel destination. In the destination editor, select the Excel connection in themanager, choose 'table or view' for data access mode and for 'name of the Excel sheet' click on new button and paste the create table command from Step 5.Map the columns appropriately in the mappings tab and you are done. Let me know if you have any questions.

You map the columns at design time. You dontneed to do that everytime the package runs. As long as the column names and data types remain the same, you dont have to do anything.

so it's impossible that after i create dynamically the excel file, in the control flow can i automatically use it as a destination file? will be any problem if i don't map it? My goal for this one is create a dynamic file in the excel and use it automatically as the destination file which runs in one package Thanks

arsonist wrote: will be any problem if i don't map it? The package will fail if you don't map it. At the very least you wont see any data in the Excel file. What we are trying to do is create an excel connection that dynamically creates an excel file under the covers. You will use the excel connection just as you would use a regular OLEDB connetion, to create your package, as if you are working with a static Excel file. Hope its clearer.

Hi Ravi,I am new to SSIS.I followed your step by step, everything was great until step 7, when I pasted the "Create table" dialog, I got this error (see below).Any idea?Thanks in advance. TITLE: Microsoft Visual Studio------------------------------The table cannot be created.------------------------------ADDITIONAL INFORMATION:The Microsoft Jet database engine could not find the object 'Employee List'. Make sure the object exists and that you spell its name and the path name correctly. (Microsoft JET Database Engine)------------------------------BUTTONS:OK------------------------------ Minh

Newcomer2k wrote:I followed your step by step, everything was great until step 7, when I pasted the "Create table" dialog, I got this error (see below).Looks like there is a mismatch between the two create table statements.Try this...At step 7, after you click the new button to create a new table, instead of pasting your create statement, use the default statement SSIS creates. You can change the table name if you want.Let me know if that works.

I think, I found my problem.The empty Excel file, must be created using MS Excel.I was creating it as an empty text file with extension xls. Anyway, I find this transformation is very flaky.It works sometimes but does NOT in other times. Thanks again,Minh

Newcomer2k wrote: Anyway, I find this transformation is very flaky.It works sometimes but does NOT in other times. Yes. I've got that feeling too.

Well i think the right way of doing it is: 1 - Create a Excel Connection with dynamic path and delay validation to true 2 - Create a File Connection with the same dynamic path and delay validation to true 3 - Use a File Task to Delete the "File Connection" pointing to the excel file in case of existing 4 - Use a Execute SQL Task and run a Create table... so that the excel file is created with the specified sheet on disk. 5 - Load the Excel file And i think that is just what is enough to achieve the goals :) Best Regards, Luis Simes

Please help, my package will contain 2 oledb source, 1) will return ExcelFilePath, ExcelFileName, SheetName, RowNumber, ColumNumber and 2) will return actual data. i want to save the data returned by 2nd OLEDB indynamic excel returned by first OLEDB. Please let me know if it is possible.

I was able to get this to work (dynamic Excel file name with date), but I am processing multiple data files per day, so I wanted to add hour and minute to the file names "..._2014.xls". But as soon as I add the hour/min to the 'ExcelFilePath' of the Connection Manager the Excel Destination task fails because it can't find the file. Any ideas? Dave

dvh wrote: I was able to get this to work (dynamic Excel file name with date), but I am processing multiple data files per day, so I wanted to add hour and minute to the file names "..._2014.xls". But as soon as I add the hour/min to the 'ExcelFilePath' of the Connection Manager the Excel Destination task fails because it can't find the file. Any ideas? Dave That should work. Could you provide the details of you package. Speceifically, the expressions and variables involved in the excel connection manager. Also see if this example helps you: http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html

What If I wanted to create a new worksheet each time in the same Excel File? How would I do that? For example I want each worksheet name to have the right date 4-27, 4-28, then 4-29 etc....Thanks

Rafael has another post: http://rafael-salas.blogspot.com/2008/03/ssis-and-dynamic-excel-destinations_01.html that shows how to do this.

uryy4me wrote: What If I wanted to create a new worksheet each time in the same Excel File? How would I do that? For example I want each worksheet name to have the right date 4-27, 4-28, then 4-29 etc....ThanksYou can use an execute sql task prior to the data flow to create the desired sheet via create table statement. Then the data flow will point to the just created sheet.

Hi Ravi g., rafael and all Your post is very articulate. Actually I have a dts package which would do the same. Migration to SSIS failed. I am recreating the SSIS package. I have an active x script , when I copy the same onto SSIS active x script task, i have a red cross mark that sayd, function not found. The active x script is as follows Function Main() Dim oConn, sFilename, sFolder ' Filename format - exyymmdd.log sFilename = "IA-Report-" & Right(Year(Now()), 2) If Month(Now()) < 10 Then sFilename = sFilename & "0" & _ Month(Now()) Else sFilename = sFilename & Month(Now()) If Day(Now()) < 10 Then sFilename = sFilename & _ "0" & Day(Now()) Else sFilename = sFilename & Day(Now()) Dim myDate Randomize myDate = Int ( Rnd * 10000 ) Dim oShell Set oShell = CreateObject("Wscript.Shell") sFolder = oShell.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Schlumberger\Ehub\ReportDataDir") sFileName = sFilename & "-" & myDate & ".xls" DTSGlobalVariables("FileName").Value = sFileName DTSGlobalVariables("FullPath").Value = sFolder & "\" & sFilename Main = DTSTaskExecResult_SuccessEnd Function. I am not able to proceed any further, can you please throw some light and guide me what to do. I know active x script task should not be used but i need to use here. Ples help me out, im very new to SSIS as well as SQL. Thanka a lot in advance,

Here is the sample expression "C:\\Temp\\Data\\" + (DT_WSTR,4)YEAR(GETDATE()) + "-" + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + "-" + RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2) + ".txt" Returns: C:\Temp\Data\2009-11-24.txt You can also Download BI xPress from Pragmaticworks and they have many expression snippets .. for quick expression building. http://www.pragmaticworks.com/Products/Business-Intelligence/BIxPress/ SSIS-Tutorials-FAQs | Convert DTS to SSIS | Document Entire BI | SSIS Tasks | BIDS Plugin

Nayan - I must ask you to STOP posting blatant advertising thinly disguised by irrelevant information in already answered posts. You are spamming this forum with junk ads for Pragmatic Works' products.I do not believe that Brian Knight is condoning or supporting your activity, and I have emailed him directly to alert him to your activities. I believe he will do what he can to contact you and ask you to refrain from your activities.You may honestly believe that Pragmatic Works' products are the best thing since sliced bread - I can't say one way or another. There may be a thread or two that could genuinely benefit from your suggestions to use BIxPress. But your seemingly erratic spamming of threads - regardless of topic - with links to that product are disruptive and not helpful.Please stop.

Hi rafael, i am very very new to SSIS, i have some issues in my production application. I tried your's and Ravi's steps but not able achieve my target. While creating excel file with static name it is working fine, but while trying to use a variable for create statement it is giving a problem. "Error at Data Flow task [Excel destination [581]]; opening a rowset for "Create Table............." failed. Check that the object exist in the database. Additional Information: Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap) Please help me.

Specify your requirements clearly-- Sathish

Dear Ravi, How can i do this for from excel source to oledb destination and create dynamic excel. could u pls explain from above thnks pls send to my id vinothlilly@gmail.com

Dear Ravi, Could u solve my following query please. I want to create a dynamic excel file every day. 1. i have excel source, i want read this data from file path using sql task and load the file import into oledb destination with schedule task, and also store the schedule date and create dynamic excel file, next day the last day record could not be load. thnks vinoth

Hi, Could anybody please tell me how to use if not exist for excel connection manager. Using execute sql task i have created the file and the sheet but I want to embed the execute Sql task in my package. I don't want to run manually once after that disable it. I want everytime it should run and if the file or sheet is not there it should create. Thanks, Shobhit

This doesn't work in 2008. When I'm creating the Exectute SQL Task (Step 5 of Ravi's solution), I can set the connection type to EXCEL but when I close the task editor and re-open it, its reset itself to OLE DB. If I run it as OLE DB, I get a failure. It also means that I need to enter a Connection Manager and when I do I get a ResultSet error.

Can anyone shed some light on a problem I am having... I have an existing Excel spreadsheet with multiple worksheets. I know the worksheet I would like to copy a SQL Table to but unfortunately every time the package runs, the columns of the table change. I have to be able to write to a specific worksheet but can't validate or map columns since it changes on each run. I have tried to think of something in a script task but not sure how to go about this? I would appreciate any help on this! Thanks!

It will be very helpfull if you can please provide us the detail steps using filetask to dynamically create excel sheet and export data int to that excel sheet.

hi I have problem in which we have to store these dynamically created excel file into database table can u suggest some method to store these file.

Hi Ravi, Thanks for your blog on creating a dynamic excel. I have one prob that is when we are creating a table in Step-5. If i execute my package the second time if fails saying that table already exists. As I am going to run this package again n again. Could you pls suggest a work around to this scenario. Thanks