Data processing extensions on SQL Server Express Edition?

I developed nice reports using custom data processing extensions. When I deployed the reports on my report server (I am using the express edition of SQL Server 2005) I was surprised to see that my reports were not rendering successfully.

After searching the web, I found this page listing the supported/unsupported features of SQL Server 2005 Express Edition: http://msdn2.microsoft.com/en-us/library/ms365166.aspx

On this page it clearly says “The Reporting Services API extensible platform for delivery, data processing, rendering, and security is not supported.”

Is there a way to get my reports to work on the express edition?

If not, which minimal version of SQL Server should a buy to get it to work (workgroup, standard or enterprise)?

Thanks for your help.

You will need Standard or Enterprise edition:

http://msdn2.microsoft.com/en-us/library/ms143761.aspx

Data Processing Extension "Edit" button

I have developed a data processing extension and want to enable the use of
the "edit" button on the data source wizard
This only seems to be enabled for the built in data providers. Do I need to
implement another interface?
Cheers
SimonHi Simon,
From your descriptions, I understood that you would like to custom the Data
Source dynamically. Have I understood you? Correct me if I was wrong.
First of all, I am not very sure whether you need a middle layer,however,
here is an example using ADO.NET Datasets in Reporting Services passing the
filter data to the correct stored procedures. I think it might help
Using ADO.NET DataSets from SQL 2000 Reporting Services
http://weblogs.asp.net/gavinjoyce/archive/2004/01/29/64339.aspx
Secondly, have you tried DataSet object? The DataSet object is central to
supporting disconnected, distributed data scenarios with ADO.NET. The
DataSet is a memory-resident representation of data that provides a
consistent relational programming model regardless of the data source. See
Topic "Using an External Dataset with Reporting Services" in Reporting
Services BooksOnline.
If I misunderstood you, please feel free to let me know further what you
want and it will be highly appreciated if you could show me the scenario:)
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||I think you have missed the point.
I have written a data processing extension implementing IDbConnection etc.
When I create a data source in report designer the box for the connection
string alows me to enter a connection string directly, however the edit
button on the right hand side is disabled. How can I enable this and have it
display the data link properties box as it does when using the sql server,
OLEDB, ODBC and Oracle data providers.
""Michael Cheng [MSFT]"" wrote:
> Hi Simon,
> From your descriptions, I understood that you would like to custom the Data
> Source dynamically. Have I understood you? Correct me if I was wrong.
> First of all, I am not very sure whether you need a middle layer,however,
> here is an example using ADO.NET Datasets in Reporting Services passing the
> filter data to the correct stored procedures. I think it might help
> Using ADO.NET DataSets from SQL 2000 Reporting Services
> http://weblogs.asp.net/gavinjoyce/archive/2004/01/29/64339.aspx
> Secondly, have you tried DataSet object? The DataSet object is central to
> supporting disconnected, distributed data scenarios with ADO.NET. The
> DataSet is a memory-resident representation of data that provides a
> consistent relational programming model regardless of the data source. See
> Topic "Using an External Dataset with Reporting Services" in Reporting
> Services BooksOnline.
> If I misunderstood you, please feel free to let me know further what you
> want and it will be highly appreciated if you could show me the scenario:)
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
>|||Hi Simon,
I have tested it on my side and it works fine. Please refer to the
following document to see whether it helps
Deploying a Data Processing Extension to Report Designer
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/
rsp_prog_extend_dataproc_9cds.asp
Deploying FsiDataExtension
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsamples/ht
m/rss_sampleapps_v1_16g2.asp
Implementing a Data Processing Extension
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/
rsp_prog_extend_dataproc_5c2q.asp
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Can you tell me what you tested?
For the FSI extension the edit button should not bring up the data link
window because it is an extension for the file system and not an OLEDB
connection.
Do you understand what I am trying to do?
I have read all the online documentation and BOL and there is no mention of
the edit button on the datasource wizard.
Simon
""Michael Cheng [MSFT]"" wrote:
> Hi Simon,
> I have tested it on my side and it works fine. Please refer to the
> following document to see whether it helps
> Deploying a Data Processing Extension to Report Designer
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/
> rsp_prog_extend_dataproc_9cds.asp
> Deploying FsiDataExtension
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsamples/ht
> m/rss_sampleapps_v1_16g2.asp
> Implementing a Data Processing Extension
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/
> rsp_prog_extend_dataproc_5c2q.asp
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>

Data Model Question

I posted to programming but thought maybe this is a btter group:
There may be some tools out there already developed that can handle this but
I haven't had success in finding them.
Essentially what I am trying to do is create a data model that can
accomodate the creation of a variety of forms to be filled out.
The end user should be able to create a question and select what type of
answer it will bne, multi select, checkbox, radio box, free text. Provide
the possible answers if needed. And then allow the form to be filled out
and the responses saved.
That part shouldn't be too tough. Forms may need to be able to be related
to other forms or multiple forms could make up 1 form to fill out. Like
form A and D.
If there is something like this already I don't want to reinvent the wheel.
Also if someone has experience with this type of model has it been good or
bad.
Thanks.You had one response. Was that not helpful? There are also some posts from
Janmar on a similar topic. Did you read those posts?|||A response in this group or the programming one? I didn't see any.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:ucBrV62lFHA.3300@.TK2MSFTNGP15.phx.gbl...
> You had one response. Was that not helpful? There are also some posts
> from
> Janmar on a similar topic. Did you read those posts?
>

Data Model Question

I posted to programming but thought maybe this is a btter group:
There may be some tools out there already developed that can handle this but
I haven't had success in finding them.
Essentially what I am trying to do is create a data model that can
accomodate the creation of a variety of forms to be filled out.
The end user should be able to create a question and select what type of
answer it will bne, multi select, checkbox, radio box, free text. Provide
the possible answers if needed. And then allow the form to be filled out
and the responses saved.
That part shouldn't be too tough. Forms may need to be able to be related
to other forms or multiple forms could make up 1 form to fill out. Like
form A and D.
If there is something like this already I don't want to reinvent the wheel.
Also if someone has experience with this type of model has it been good or
bad.
Thanks.
You had one response. Was that not helpful? There are also some posts from
Janmar on a similar topic. Did you read those posts?
|||A response in this group or the programming one? I didn't see any.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:ucBrV62lFHA.3300@.TK2MSFTNGP15.phx.gbl...
> You had one response. Was that not helpful? There are also some posts
> from
> Janmar on a similar topic. Did you read those posts?
>

Data Model Question

I posted to programming but thought maybe this is a btter group:
There may be some tools out there already developed that can handle this but
I haven't had success in finding them.
Essentially what I am trying to do is create a data model that can
accomodate the creation of a variety of forms to be filled out.
The end user should be able to create a question and select what type of
answer it will bne, multi select, checkbox, radio box, free text. Provide
the possible answers if needed. And then allow the form to be filled out
and the responses saved.
That part shouldn't be too tough. Forms may need to be able to be related
to other forms or multiple forms could make up 1 form to fill out. Like
form A and D.
If there is something like this already I don't want to reinvent the wheel.
Also if someone has experience with this type of model has it been good or
bad.
Thanks.You had one response. Was that not helpful? There are also some posts from
Janmar on a similar topic. Did you read those posts?|||A response in this group or the programming one? I didn't see any.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:ucBrV62lFHA.3300@.TK2MSFTNGP15.phx.gbl...
> You had one response. Was that not helpful? There are also some posts
> from
> Janmar on a similar topic. Did you read those posts?
>

Data Model Help

There may be some tools out there already developed that can handle this but
I haven't had success in finding them.
Essentially what I am trying to do is create a data model that can
accomodate the creation of a variety of forms to be filled out.
The end user should be able to create a question and select what type of
answer it will bne, multi select, checkbox, radio box, free text. Provide
the possible answers if needed. And then allow the form to be filled out
and the responses saved.
That part shouldn't be too tough. Forms may need to be able to be related
to other forms or multiple forms could make up 1 form to fill out. Like
form A and D.
If there is something like this already I don't want to reinvent the wheel.
Also if someone has experience with this type of model has it been good or
bad.
Thanks.Hi
http://www.databaseanswers.org/data_models/ may be of help
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Brian" <brian@.nospam.com> wrote in message
news:%23Kbg6XflFHA.2180@.TK2MSFTNGP15.phx.gbl...
> There may be some tools out there already developed that can handle this
> but
> I haven't had success in finding them.
> Essentially what I am trying to do is create a data model that can
> accomodate the creation of a variety of forms to be filled out.
> The end user should be able to create a question and select what type of
> answer it will bne, multi select, checkbox, radio box, free text. Provide
> the possible answers if needed. And then allow the form to be filled out
> and the responses saved.
> That part shouldn't be too tough. Forms may need to be able to be related
> to other forms or multiple forms could make up 1 form to fill out. Like
> form A and D.
> If there is something like this already I don't want to reinvent the
> wheel.
> Also if someone has experience with this type of model has it been good or
> bad.
> Thanks.
>

Data flow task running very slow

Hello,

I developed an SSIS package doing a nightly load into a data warehouse. We have an 8 hour loading window - currently the package takes 16 hours to complete.

I isolated the problem to a Data Flow task where +-35% of the time is spent. This task is pretty straight forward:

- OLE DB source, reading +- 800,000 rows from a SQL server database

- 13 Lookups in sequence, to get surrogate keys from dimension tables. Lookups are all on GUIDS.

- An aggregation

- OLEDB target, fact table in a SQL server database.

It seems unreasonable for the this task to take over 5 hours. It spends the majority of time on the lookups - not so much at target, source and aggregation.

Any comments and advice will be greatly appreciated.

Thanks.

(PS some machine details:

OS Name Microsoft(R) Windows(R) Server 2003, Standard Edition
Version 5.2.3790 Service Pack 1 Build 3790
Other OS Description Not Available
OS Manufacturer Microsoft Corporation
System Name ARK-SQL
System Manufacturer HP
System Model ProLiant DL380 G5
System Type X86-based PC
Processor x86 Family 6 Model 15 Stepping 6 GenuineIntel ~1866 Mhz
Processor x86 Family 6 Model 15 Stepping 6 GenuineIntel ~1866 Mhz
BIOS Version/Date HP P56, 9/18/2006
SMBIOS Version 2.3
Windows Directory C:\WINDOWS
System Directory C:\WINDOWS\system32
Boot Device \Device\HarddiskVolume1
Locale United States
Hardware Abstraction Layer Version = "5.2.3790.1830 (srv03_sp1_rtm.050324-1447)"
User Name Not Available
Time Zone South Africa Standard Time
Total Physical Memory 3,327.30 MB
Available Physical Memory 938.20 MB
Total Virtual Memory 1.10 GB
Available Virtual Memory 2.78 GB
Page File Space 2.00 GB
Page File C:\pagefile.sys)

How many rows are the lookups caching? Are you selecting the whole table (All columns) or are you using a SQL query to specify only the columns you need.

Are you sure it is not your dest that is slow receiving the rows?
If it is, SSIS will slow down the rows retrieved from the source making it appear as if it is something else slowing it down.

Check the lookups. Are as few columns as possible being selected.
Push your rows into nothing such as a Konesans Trash Destination. Does it appear faster?

Also, if your lookup is caching alot of rows and the key you are caching is a GUID, that's a chunk of work to do.|||

You need to find where the bottleneck is. You could start measuring how fast the dataflow 'reads from source'; then how fast it does the transformations (since you have a fair amount of transformations, I would measure a several points); and finally measure the how fast it writes into the destination.

few tips:

The lookups could slow down performance if you are using partial cache. So, limit the number of columns and rows (by providing a select statement with a where clause if possible) and use full cache mode. This approach could generate another problem if memory in server is limited.

See if you can move the aggregation upstream and or limit the number of columns to be used as 'group by'. In general aggregations will perform better if the number of columns with fewer columns in the 'group by'.

If you use an OLE DB destination, try to stick with 'fast load' and set the interval commit to an acceptable range

Use DB profiler tools to measure the performance of each SQL statement use in the data flow (OLE DB source, lookups, etc)

This white paper has some other information

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx|||

I would also highly recommend taking a read of this:

Donald Farmer's Technet webcast

(http://blogs.conchango.com/jamiethomson/archive/2006/06/14/SSIS_3A00_-Donald-Farmer_2700_s-Technet-webcast.aspx)

-Jamie

|||

Hi Crispin,

Thanks for your response.

I solved the problem last night. The key was in the caching.

I modify the SQL statement in most of my lookups to handle inferred fact entries. I.e. a sale against an unknown customer gets linked to the 'UNKNOWN CUTOMER' dimension table entry. It seems that SSIS sets the caching to partial when you modify the lookup SQL. I assume the lookup had to go back to disk quite often and therefore high cost for big dimension tables (...of which I have quite a few).

I am achieving a +-500% performance increase by keeping the Lookup transform as it is and by setting the cahcing to full. I treat the inferred fact entries at a later stage in stead of at the point of lookup.

|||Good to hear it has approved, however...

The lookup does not change to partial cache when you specify a statement. Not sure why you say it has done this. Was the checkbox checked?

On the partial cache thing, you are correct. The lookup will check it's cache, if the key is found, use it, else run the query against SQL. This can become dog slow. Only time it has been a good thing, for me at least, is when you know your facts are going to be a very small portion of a really large dimension. That way, the first few queries are slow but you don't have to cache 13 million rows only to use 1000.