February 18, 2009 at 3:32 pm
Hi,
A supplied excel worksheet has the sheet name Sheet1 changed to a date format looking name each day. eg it is named 19.02.2009 today.
Getting the user to stop doing this seems impossible.
Is it possible at run time to dynamically change the name of the OpenRowset custom property of the data flow component? ( I hope that is the correct terminology).
Currently the property is set to "Sheet1$". I haven't noticed in any of the connection strings any reference to the sheetname or any obvious places where I might change it on the fly...
Thanks,
February 18, 2009 at 5:45 pm
Hi,
I worked it eventually
Create a variable like:
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("dd", GetDate()), 2) + "." + RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mm", GetDate()), 2) + "." + (DT_STR, 4, 1252)DATEPART("yyyy", GetDate()) + "$"
Then in the Custom properties of the DataSource Component, select from "AccessMode" the option "OpenRowset From Variable" and enter your variable name "User::sheetname" in the "OpenRowsetVariable" property.
NB: Sheet names must end with a dollar sign $
February 18, 2009 at 7:35 pm
Another approach for when the sheets are not logically named is:
In a for each loop,
on the collection page,
use enumerator for each ADO.NET Schema Rowset Enumerator
set the Connection to your Excel file
make Schema Tables (this corresponds to sheet)
on the variable mappings page,
list the variable to hold the sheet name
use index 2 (I do not know why 2 works)
Check that the sheet name is one you want (ends in $ or $').
If so, do a data flow task to read the sheet and process it.
In the data flow task that reads the Excel sheet:
Set up an Excel source with data access mode: table name or view name variable
and select the variable that holds the sheet name in the variable name box.
It is also possible to build a query to read part of a sheet.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply