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 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....
|||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 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 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.
|||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, you can 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
|||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:
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.
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
|||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.
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.
|||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
|||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.
No comments:
Post a Comment