Creating a dynamic excel file

Question

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

Answer 1

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.

Answer 2

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

Answer 3

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 approach is 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 is configured 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....

 

Answer 4

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 approach is 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 is configured 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 2^nd 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 uses an 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.

 

Answer 5

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.

Answer 6

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, you can set the filename property dynamically instead of the connection string property, its simpler and more readable.

 

 

 

Answer 7

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.

Answer 8

Sure. I was planning to post a summary of my findings anyway.

I'll be posting it soon.

 

Answer 9

This example is useful for loading data from an OLEDB source into a dynamically created Excel file.

NOTE:
This is 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 root
directory 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 directory
and 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 button
with "..." 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 the
manager, 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.

 

 

Answer 10

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

Answer 11

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: This is 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 root directory 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 directory and 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 button with "..." 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 the manager, 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.

Answer 12

You map the columns at design time. You dont need 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.

 

Answer 13

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

 

Answer 14

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.

 

Answer 15

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

Answer 16

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.

Answer 17

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

Answer 18

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.

Answer 19

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 Simões

Answer 20

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 in dynamic excel returned by first OLEDB.

 

Please let me know if it is possible.

 

Answer 21

 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

Answer 22

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

Answer 23

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

Answer 24

Rafael has another post: http://rafael-salas.blogspot.com/2008/03/ssis-and-dynamic-excel-destinations_01.html

 that shows how to do this.

Answer 25

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

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

Answer 26

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_Success
End 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,

Answer 27

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

Answer 28

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.

---

Answer 29

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.

 

Answer 30

Specify your requirements clearly

---

-- Sathish

Answer 31

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 [e-mail]

Answer 32

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

Answer 33

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

 

Answer 34

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.

 

Answer 35

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!

Answer 36

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.

 

 

 

Answer 37

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.

Answer 38

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