January 9, 2008 at 4:46 pm
SQL Server 2005
January 9, 2008 at 5:31 pm
Great, It works perfectly.
I just have to put together the mechanism with the variables.
so instead of an URL not supported by JET, use an UNC
In my example I simply have to use variables or configurations to build the path.
instead of the original
I use this
\\smportal.onsemi.com\C10\Reporting Projects\Development Process and Templates\CalendarWeeks.xls
BI Guy
January 10, 2008 at 2:22 am
Hi,
I finaly did it and it works realy great.
There is one thing I had to change.
I had a problem (or I didn't get it right) with Execute task (to create new TAB within same exec file).
When I changed variable ExcelTab to something like this
"CREATE TABLE `" + @[User::region] + "` (
`my_field` VARCHAR(50)
)"
works perfectly
Thanks for the article. I realy find a lot of interesting stuff in it.
radovan
January 10, 2008 at 4:16 am
Can you please list all variables you used?
I was able as well to make the package work by using "CREATE TABLE `" + @[User::region] + "` (
`my_field` VARCHAR(50)
)" before adding the Dataflow task. When I added the dataflow task and assigned the variable to the Excel Destination it didn't alow me. what did you use for the variable ‘Subgroups’ ?
Thanks in advance
January 10, 2008 at 5:05 am
Hi,
I little change sample, so I use this variables:
1)
FilePath='c:\temp\sample.xls'
(but, I can dynamicly create file name through expression properties, like
"c:\\temp\\test_" + substring((DT_STR, 50,1250) GETDATE(),1,10)+".xls"
if I need file name
c:\temp\test_2008-01-10.xls
EvaluateAsExpression property needs to be 'True'
2)
In Excel connection manager in Expression Property I set 'excelFilePath' to @[User::FilePath]
3)
ExcelTab Variable is set throuh Expression to
(EvaluateAsExpression property needs to be 'True')
"CREATE TABLE `" + @[User::region] + "` (
`my_field` VARCHAR(50)
)"
4) In Execute SQL Task, I Set
Connection Type to 'Excel'
Connection to existing Excel connection manager (created in step 2)
SQl source type to 'Variable'
SourceVariable to 'User::ExcelTab' (created in step 3)
Execute workws perfectly if excel file with @[User::region] doesn't exists.
It will fail if it exists.
radovan
January 10, 2008 at 8:01 am
Thanks, but this means you didn't use the DataFlow Task. What you mentioned is only creating several files with one sheet or one file with several sheets. I just wanted to see if you were able to do the whole sample where we can create multiple excel files with multiple sheets dynamically as the article explains.
I think the article is not complete or there is something missing somewhere.
Any way thanks
January 10, 2008 at 8:11 am
Hmmm, something must be lost in the translation... 🙂 This solution indeed will create multiple files and each can have multiple tabs/spreadsheets with their own specific data.
The key to that really is the population of the initial 'cursors' which is done in the Execute Sql task before the Foreach Loop. By populating the Group cursor from a table in the database containing the names for the files and populating the Sub-Group cursor from a different table in the database containg the names for the tabs/spreadsheets WITHIN the Groups you create the files and tabs/spreadsheets within the tabs.
Next the specific Select statement against the rest of the database uses the Group and/or the Sub-Group cursor value to select the correct data for just that Sub-group to populate the correct tab/spreadsheet. It really depends on your database schema. In our case we had to create a Group only table as all the rest of the data is in the schema.
If I may, it is a matter of buckets of data within buckets of data and then using those buckets to pull what you want. Hoping this helps a bit....
January 10, 2008 at 8:27 am
Do you have a complete sample where you can e-mail it and check what is missing? Where the DataFlow is included as well. My problem is with the Destination Excel File where I can't define the variable. it asks for column mapping and there are no columns in the variable. 🙂
Thanks
January 10, 2008 at 8:31 am
Hi,
I di use use dataflowTask , and went through cursor.
Problem was in executeSql task within dataFlowtask and ExcelTab variable.
When I changed from file name into Create Table .... (using sample from this link) it worked.
http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-into-dynamic_22.html
radovan
January 10, 2008 at 8:38 am
ah ok; I posted this link but the sample here is a little bit different.
January 10, 2008 at 11:05 am
Someone asked for all the variables so we go:
ExcelTab - which populates the excel tab/spreadsheet. This value actually comes from the query in the first Execute Sql Task (Called Create New File with tabs) before the Foreach Loop.
The sql is: select distinct replace(a.Region,' ',''), b.SubsidiaryName
from dbo.Marie_Regions a
INNER JOIN dbo.Geography b
ON a.region = b.region
which populates a SINGLE object called 'List of Countries' with two sets of values. These values are references as '0' and '1'.
When you are setting up the Foreach Loop Set the Variables equal to one of the populated value lists in the object 'List of Countries'. What does that mean? Well, define the variable 'ListofGroups' and set the INDEX as 1
ExcelFileName
SubDataSet
January 10, 2008 at 11:26 am
Hi,
What you mentioned is already clear. The place where we have problems in is the execute SQL Task in the Foreach loop and the Excel Destination in the DataFlow Task.
Thanks for your remarks
January 10, 2008 at 11:27 am
Sorry, wrong button!
To continue:
By setting the pre-defined variable 'Listof Groups' equal to INDEX 0 you then have all the values you populated in the 'List of Countries' object from the select statement for Region.
In other words....whatever is in the initial select statement is populated in order (0, 1, 2, 3) into a BIG cursor/bucket. You then reference each set of data, list of data, within the Foreach Loop in the Variable Mappings section of the Foreach Loop Editor by attaching a variable to an INDEX number which is the populated order.
ok,
other variables.
ExcelTab: Is populated using a standard CREATE table that is used when pushing from sql server to excel such as:
"CREATE TABLE" + " " + "`"+@[User::ListofSubgroups] +"`"+ " " + "(
`Region` NVARCHAR(35),
`Subsidiary` NVARCHAR(40),
`Value1` NVARCHAR(200),
`Value2` NVARCHAR(50),
`Value3` NVARCHAR(15)
)"
This creates the tab/spreadsheet with the proper subgroup name and adds the titles across the columns.
SubDataSet - Is used in the OLE DB Source component of the Data Flow Task. Placed in the 'Variable name' box with syntax: User::SubDataSet. Populate it with a select from your database. Make sure the columns match the column headings in your Create table statement above when creating the tab/spreadsheet otherwise this will fail at this point.
Hope this helps a little?
Thanks for everyone's comments....definitely help to hone the article and help each other.
January 10, 2008 at 12:08 pm
ok, you are right with all what you mentioned but the problem lies by assigning the Excel Destination in the DataFlow Task. When I assign the variablename it gives me the error map columns.
What is the problem with this step? and how it should be configured?
Thanks a lot for the explanations.
January 10, 2008 at 12:23 pm
When creating the solution did you have an excel workbook with at least one spreadsheet in it containing the appropriate headings?
I believe we all ran into the same thing and that seemed to resolve the issue of not being able to add the variable during the Data Flow Task. ONCE able to add the variable then the 'sample' workbook is deleted.
As an aside...really want to thank people for all the comments...made me go back and DOUBLE check that the solution runs...and in fact it is running right now for two Groups with over 12 spreadsheets in each etc etc..but I can see how the document could be much tighter for next time. 🙂 (and unfortunately longer)
Viewing 15 posts - 16 through 30 (of 58 total)
You must be logged in to reply to this topic. Login to reply