I have a spreadsheet that's designed to look like a form. Does anyone know how I would create a SSIS package to extract specific cell values?
TIA
Have you tried using the Excel Source Adapter (http://msdn2.microsoft.com/en-us/library/ms181175.aspx)
Thanks
Anjan.
Are you specifying Import Mode by using IMEX=1 in your connection string? This forces all data to text.
The Excel driver does store what it believes to be text data preceded by the apostrophe, to force the Excel application in turn to recognize it without question as text values. This implies to me that the columns in question contain at least some values that the driver is interpreting as text.
The BOL topic on the Excel Source contains some information that explains some of the behaviors of the Jet Excel driver.
-Doug
|||Doug,
Thanks a lot for advice. I tried to put IMEX=0 (2) into a connection string, but it did not solve the problem. I wonder what the default is if IMEX parameter is not specified?
Sergey
|||Sergey,
I was not encouraging the use of Import Mode...on the contrary, this forces everything to text and would presumably add the opening apostrophe everywhere.
You may want to try allowing the Import and Export Wizard to build your "base" package and add necessary Data Conversion tasks etc. for you. Then you can customize the package afterward.
Be careful to check that the driver is recognizing your numeric columns as numeric, by examining the data types of columns in the various components.
-Doug
Hi All!
I am trying to create a table destination to excel which fails with the following error that " Table cannot be created " System Error in Field definition" where as the same works in the Falt File Destination. What could be the problem?
Thanks
jas
It maybe due to the fact that excel has in built data types and column sizes attached to the columns whereas a flat file does not have that.Try creating a table first and then map the excel columns to it to extract the records from the excel spreadsheet. When you import into a staging table, it is best to just keep the columns as varchar(255). If you have fields like remarks field that could be even bigger, you might want to customize your staging table.sql
Hello,
I have a question regarding which expressions need to be set to have the excel connection manager be able to create/update a file dynamically. I'm reading a path in from a database and storing it in a variable. The path is just the location of an excel file. What I would like to have happen is that the excel connection manager be dynamic so that if I wanted to change where this file went or the name I could simply change the value in the database.
I've tried setting the expressions, in the Excel connection Manager: ConnectionString,ExcelFilePath, and Servername to the variable \\path\folder\file.xls with no luck. The ConnectionString seems to be a little funny because it contains Provider=Microsoft.Jet.OLEDB.4.0;Data source = ; Extended Properties =; HDR=YES", so I don't know if I can just use that expression and ignore the others or what.
Any help would appreciated,
Phil
Phil,
I think you are on the right track. In order to make the path and file name dynamic, you just need to use an expression against ExcelFilePath property. The only caveat thought is that even when the ExcelFilePath is dynamic; that it won't create the file 'on the fly'. The work around is to use an Execute SQL task to create it before it gets used in the dataflow. I have an example of that in my blog:
http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html
I hope you find it helpful
|||
Yea, you were right. It's just a matter of setting the expression in the control flow instead of the data flow. I really appreciate it.
Phil
sqlHi, there,
I am working on Excel as a front-end to a SSAS2005 cube. I intended to create a simple add-in that allows users to write back to the cube. Users will first retrieve data from the cube to pivot table and write back to the cube from the pivot table. I can find some sample source based on adodb and AS2000 but not adomd .net and SSAS2005. For the time being, information on SSAS2005 write-back from Excel seems very little. Not much references that I can refer to.
Anyone can shed some light on this? I would appreciate it if you can provide me some sample source in VBA using adomd .net or others that can demonstrate the write-back to a SSAS2005 cube. It would be great if the sample will be based on the AdventureWorksDW cube.
Thanks in advance.
Regards,
Yong Hwee
You have code that connects Excel 2003 with SSAS2005 for writeback in this book: "MS SQL Server 2005 Analysis Services 2005, Step By Step", chapter 10.
The code saples for writeback I have seen are in books, like this one.
Regards
Thomas Ivarsson
|||Hi, Thomas,
Thank you for your reply. I will try to look for the book in my local library. One more thing, do you come across any reference on the Excel add-in, Cube Analysis particularly in the What-if Analysis other than the published technical whitepaper?
Thank you.
Regards,
Yong Hwee
|||Regarding the Excel Add-In. No! I have only downloaded and tried it.
Have you heard that MS is working on a new server for budgeting and financial modelling?
ProClarity and Business Scorecard server will also be a part in this solution.
Regards
Thomas Ivarsson
|||Hi. Thomas is referring to the new PerformancePoint sevrer from Microsoft. Here's a public posting on the topic.
http://office.microsoft.com/en-au/FX101550371033.aspx
Thomas - we should catch up sometime.
PaulG
|||
Hi, Thomas and Paul,
Thanks for your replies. FYI, I am working on a DW/BI project on SQL Server 2005 Enterprise. Main deliverables are a number of monthly reports. I intended to make use of SSAS 2005 to deliver dynamic reports - users are able to produce reports on their own anytime and they are free to pivot. Of course, one of the requirements is to be able to write back from Excel frontend. I found that there are not much references on this subject.
BTW, thanks for your help and SQL Server 2005 is a great product.
Thank you.
Regards,
Yong Hwee
Hi, there,
I am working on Excel as a front-end to a SSAS2005 cube. I intended to create a simple add-in that allows users to write back to the cube. Users will first retrieve data from the cube to pivot table and write back to the cube from the pivot table. I can find some sample source based on adodb and AS2000 but not adomd .net and SSAS2005. For the time being, information on SSAS2005 write-back from Excel seems very little. Not much references that I can refer to.
Anyone can shed some light on this? I would appreciate it if you can provide me some sample source in VBA using adomd .net or others that can demonstrate the write-back to a SSAS2005 cube. It would be great if the sample will be based on the AdventureWorksDW cube.
Thanks in advance.
Regards,
Yong Hwee
You have code that connects Excel 2003 with SSAS2005 for writeback in this book: "MS SQL Server 2005 Analysis Services 2005, Step By Step", chapter 10.
The code saples for writeback I have seen are in books, like this one.
Regards
Thomas Ivarsson
|||Hi, Thomas,
Thank you for your reply. I will try to look for the book in my local library. One more thing, do you come across any reference on the Excel add-in, Cube Analysis particularly in the What-if Analysis other than the published technical whitepaper?
Thank you.
Regards,
Yong Hwee
|||Regarding the Excel Add-In. No! I have only downloaded and tried it.
Have you heard that MS is working on a new server for budgeting and financial modelling?
ProClarity and Business Scorecard server will also be a part in this solution.
Regards
Thomas Ivarsson
|||Hi. Thomas is referring to the new PerformancePoint sevrer from Microsoft. Here's a public posting on the topic.
http://office.microsoft.com/en-au/FX101550371033.aspx
Thomas - we should catch up sometime.
PaulG
|||Hi, Thomas and Paul,
Thanks for your replies. FYI, I am working on a DW/BI project on SQL Server 2005 Enterprise. Main deliverables are a number of monthly reports. I intended to make use of SSAS 2005 to deliver dynamic reports - users are able to produce reports on their own anytime and they are free to pivot. Of course, one of the requirements is to be able to write back from Excel frontend. I found that there are not much references on this subject.
BTW, thanks for your help and SQL Server 2005 is a great product.
Thank you.
Regards,
Yong Hwee
Hi,
I am trying to model data in analysis services with the Advance Create Mining Model function in the excel addin. I am having trouble creating an association model that works like the Associate button above the Advanced button.
The format of my data is like this
OrderID Product
100 Bike
100 Helmet
100 Shoes
200 Helmet
200 basketball
200 Bat
300 Shoes
300 Socks
The associate button works perfectly since it asks me which column is the transaction id (orderid) and which column I am trying to predict (product). The advanced create mining model asks me to determine what the columns are...
OrderID=key Product=Input+Predict?
When I run the advance create mining model associate, I get a browser that gives me no rules and the support for only one item itemset (each product but no combination of products).
Does anyone know what I have to do to get it to work like the associate button?
The Associate task performs a trick in defining the mining model. For data like yours, the model is defined as:
(
OrderID TEXT KEY,
Product_Table TABLE PREDICT
(
Product TEXT KEY
)
)
(it contains a nested Product_Table column, which contains all the products for one given order ID).
After that, in training, the Excel table is joined with itself to generate the shape required for model, which is :
OrderID Products (table)
100 Bike
Helmet
Shoes
The Advanced Create Mining Model functionality does not allow modeling of nested tables. Consequently, what you get by running the Advanced task is a model like:
(
OrderID TEXT KEY,
Product TEXT DISCRETE PREDICT
)
which cannot find any interesting rules.
Here is an easy workaround for this problem:
- start by using the Associate task to create a model with the appropriate modeling (nested table)
- use Advanced\Add Model To Structure to add a new model in the same mining structure (this allows you to customize your model, set different parameters etc.) on top of the same data as the first model.
- using the Manage Models task, delete the original model and use the newly created one
Alternatively, you could use DMX statements to define and train the model. The DMX statements would have to be entered manually (choose Query\Advanced then Edit Query to get to manual editing of the DMX statement). Please let me know if you need details for this
|||Hello Bogdan,
I am still having difficulty connecting to analysis services data sourse. I have tried the easy workaround and I have done all of the steps above. I am a little confused as to what i should do after i have deleted the old model.
I went to manage models->Process this mining model with new data->Selected the data i wanted from analysis services
The dialog box prompted me to specify mapping between structure columns and input columns.
Mining Columns Table Columns
OrderID OrderID
Product_Table Choices: (OrderID, Product) No Product_Table
I tried only having the OrderID in the table column (leaving the other choice blank) and I ended up with "Error too many attributes for counting correlations"
Using OrderID and Product in the table column gave me: Error (Data Mining): INSERT INTO error: The '[Product_Table],[Product]' nested table key column is not bound to an input rowset column.
Can you give me the details for the DMX statements?
Thanks,
Davy
|||First -- creating a new model in the same mining structure will process the new model with the structure data. There is no need to re-process it with new data (the model is already processed). Once you delete the (sibling) model generated by the Association task, you have the model you just created and you can start using it.
Now, the dialog for Process Model with new Data, like most of the features in the Excel add-in, is designed mostly for the tabular data in Excel (i.e. not for nested tables). If you need to process the model with new data, the easiest way is to run the workaround again (use Association task to get the model and structure, then add your own model to the structure, using the algorithm and parameters of your choice).
Do you want to get DMX statements to do this task manually?
|||Dear Bogdan,
I am interested in using the association algorithm on data that has not been imported into Excel. The association algorithm is the only algorithm that doesn't allow me to mine data from a nested table on a server. The other algorithm buttons (Classify,Estimate,Forecast,Cluster) allow me to mine data that hasn't been imported into excel. (Will future versions of the add-in include this option for the associate algorithm?)
Right now, I am importing 1MM transactions into Excel (Due to the 1MM row limit) and using the association algorithm button to mine/browse the data. I want to test my hypothesis from this 1MM transactions with 100MM new transactions from a nested table in analysis services to confirm my hypothesis (My company has millions of transactions daily).
Is there a DMX code where I can input:
The Connection
The Command Query ie.
Select Top 1000000 "OrderID","Product","DateID"
From "dwMBA"."dbo"."vOrderItems"
and get the browse window (Support,Rules,Network)
I am unfamiliar with DMX
Davy
|||Now I understand.
One solution is to create the model once using BI Developer Studio (the development tool coming with Analysis Services). Then, the model can be re-processed at any time even from Excel. You can use Managed to Clean the structure and reprocess the structure with original data. Further more, in your BI Development Studio solution you could:
- create a data source view with a named query which returns only the transactions in the last 10 days
(e.g SELECT * FROM vOrderItems WHERE DateID > DATEADD(day, -10, GETDATE()) )
- define a mining structure on top of this named query. The mining structure will use the named query as both case and nested table
- build an Association Rules mining model
- periodically, use Manage from Excel add-ins to clear the mining structure data (unprocess), then process with original data (effectively using the most recent data) or Browse to inspect the model's rules
Another possible solution, using only Excel add-ins: use the Associate task to create a model with necessary flags and parameters. After that, clear the mining structure/model and reprocess it using data from the relational database (any number of rows, because the data will nto be copied to Excel).
Here is the step-by-step solution
1. Make sure on your Analysis Services server, in the current database, there is a data source object pointing to your relational table (the one containing the transactions)
Creating such a datasource only happens once and establishes a connection between Analysis Services and your database. You could create such a data source object using the BI Dev Studio. If your database is SQL Server 2005, you could also create such a datasource from Excel add-ins. Anyway, let me know if you need help on this.
2. Just as you started, fetch a small sample of data (number of rows does not matter at this point) and copy this data in Excel. The data must contain, based on your query, at least the OrderID (transaction identifier) and Product (transaction item) columns
3. Use Associate to create an Association Rules model on top of your sample data. Let's assume you create a model named ARM in a structure called ARS.
4. Once the model is deployed on the server, use Manage Models\Clear this mining structure on the ARS structure to unprocess it.
5. Click Query
6. Click Advanced ... (bottom left of the dialog)
7. Click Edit Query (Click Yes when a warning asks you if you want to continue)
8. Paste the query below, after modifying it to include your data source name
INSERT INTO MINING STRUCTURE [ARS]
(
[OrderID],
[Product_Table]
(
SKIP,
[Product]
)
)
SHAPE {
OpenQuery([Adventure Works DW], 'SELECT OrderID
FROM vOrderItems ORDER BY OrderID') }
APPEND
( {
OpenQuery([Adventure Works DW], 'SELECT OrderID, Product
FROM vOrderItems ORDER BY OrderID') }
RELATE
[OrderNumber] TO
[OrderNumber]
) AS
[Model_Table]
Note the ORDER BY fragments on the relational queries (required for the statement to work correctly) and the SKIP keyword which indicates the statement that OrderID from the second query is only used to shape the rowsets
9. Click Finish
10. Choose New Worksheet or Existing Worksheet -- it does not matter (this query does not return results)
11. After the execution completes, use Browse to check your patterns
|||Do you have an email I can private message you?|||Sure: bogdanc at microsoft dot comsql
I want to query my excel source sheet with a paramater so i create a sql statement like
select * from xxx where name like 'fred%'
Then I want to parameterise it with
select * from xxx where name like @.Name or select * from xxx where name like ?
both allow me to map the Parameter neither syntax works in the data flow or preview but ? still works in the build query window !
Anyone know the correct Param declaration for the Excel Source (i believe it should be oledb @.Name declaration ?
is this a bug or am i missing something here ...
Cheers
Colin
Colin,
Could you provide more details about the error you are receiving and data type of the column you are mapping the parameter to?
I just did a quick test and was able to run a query with a parameter out of a Excel. The query looks like:
SELECT SurrogateKey, DatabaseSource, Acct
FROM ['YTD AP Trans$']
WHERE (RowNumber >?)
The parameter mapping is: Parameter0 --> User: FilterVariable
FilterVAriable is a package Variable string and its values is '10' (yes the quotes are part of the variable value)
Rafael Salas
|||my actual param was called @.venue, when i left it as param 0 it worked ok.
I deleted the params from the property browser, recreated the parameter left the name parameter0 and it worked ok
thanks for posting
I want to query my excel source sheet with a paramater so i create a sql statement like
select * from xxx where name like 'fred%'
Then I want to parameterise it with
select * from xxx where name like @.Name or select * from xxx where name like ?
both allow me to map the Parameter neither syntax works in the data flow or preview but ? still works in the build query window !
Anyone know the correct Param declaration for the Excel Source (i believe it should be oledb @.Name declaration ?
is this a bug or am i missing something here ...
Cheers
Colin
Colin,
Could you provide more details about the error you are receiving and data type of the column you are mapping the parameter to?
I just did a quick test and was able to run a query with a parameter out of a Excel. The query looks like:
SELECT SurrogateKey, DatabaseSource, Acct
FROM ['YTD AP Trans$']
WHERE (RowNumber >?)
The parameter mapping is: Parameter0 --> User: FilterVariable
FilterVAriable is a package Variable string and its values is '10' (yes the quotes are part of the variable value)
Rafael Salas
|||
my actual param was called @.venue, when i left it as param 0 it worked ok.
I deleted the params from the property browser, recreated the parameter left the name parameter0 and it worked ok
thanks for posting
How to Excecute SQL Script in Exrpess edition?
I wanna create a database using a lot of SQL script, but I don't know how to use it just like I used to do in the Query Analyser of SQL Server 2000.
You can use the sql ,amagement console for the express system... this is a free download you can get from the microsoft downloads site.
Here is a small article that I put together for installing the management tools, and an article on some links for the sql express product.
|||WHERE did your links go?How to Excecute SQL Script in Exrpess edition?
I wanna create a database using a lot of SQL script, but I don't know how to use it just like I used to do in the Query Analyser of SQL Server 2000.
You can use the sql ,amagement console for the express system... this is a free download you can get from the microsoft downloads site.
Here is a small article that I put together for installing the management tools, and an article on some links for the sql express product.
|||WHERE did your links go?Hi
I am looking at documentation of CREATE CERTIFICATE statement. I am having hard time in understanding if I want to create CERTIFICATE with above mentioned options, how I am supposed to create either PRIVATE KEY file or EXECUTABLE file. any example would be really helpful for what I am doing here.
thanks
Satya
Take a look at makecert: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cptools/html/cpgrfcertificatecreationtoolmakecertexe.asp. This utility generates certificates in those two types of files.
But note that you don't need to create those files to create a certificate. Those options are only used to import a certificate from such files. You can create a certificate within SQL Server by not using those options. You can find several examples on my blog, for example: http://blogs.msdn.com/lcris/archive/2005/06/10/428178.aspx.
Also, you can create those files by backing up a certificate created in SQL Server. See syntax for BACKUP CERTIFICATE: http://msdn2.microsoft.com/en-us/library/ms178578(SQL.90).aspx.
Thanks
Laurentiu
Hi,
Please show me a example of how to create and use a Parameter in DTS Package (SQL Server 2000)
Thanks,
Vivek Gupta
Rather than explicitly pass a parameter to the DTS package to pass to some stored procedure, why not create a a parameter table to pass in the value. Suppose you want to pass in an integer, then create a ParamInt table, with columns of Id (Integer non-identity primary key, ParameterName Varchar(50), ParameterValue Int default value 0.
Insert your value (or values) as rows into this table - you stored procedures run by the DTS package can then pickup their parameters from the appropriate row of this table.
HTH
I need to create database for a university. I was looking for some
kind of example from the net but haven't had much luck at finding one
yet. Can anyone help, please.david_a_morrison@.hotmail.com (David) wrote in
news:8c8ad58b.0406120341.32515857@.posting.google.c om:
> I need to create database for a university.
PSI ...
--
Lyle
(for e-mail refer to http://ffdba.com/)|||Hello
Educatiional institutions usually require very large and complex
databases, which also should meet rules applied by governing bodies.
You cannot find examples on the net because software houses do not
publish examples of their systems and it is not a task for an
individual. You can have a look at http://www.fdlearning.com/. We (a
college of futher education) use a database system from this supplier.
It will give you a feel of the scale of your task.
Hope it will help you not to start on this project.
Galina
david_a_morrison@.hotmail.com (David) wrote in message news:<8c8ad58b.0406120341.32515857@.posting.google.com>...
> Can anyone help!
> I need to create database for a university. I was looking for some
> kind of example from the net but haven't had much luck at finding one
> yet. Can anyone help, please.
