How to execute a SSIS dtsx package from an asp.net 2.0 application?

I have a SSIS package that I want users to be able to execute by clicking a button on an a web page. The package does not require any parameters to be passed to it. Previously I've executed DTS packages without any problems but after a fair bit of investigation and trawling the net I've not found a way to do this successfully with SSIS. Some code I've tried -

 Dim app As New Application() ' ' Load package from file system ' Dim package As Package = app.LoadPackage("c:\ssis\Package.dtsx", Nothing) 'package.ImportConfigurationFile("c:\ExamplePackage.dtsConfig") 'Dim vars As Variables = package.Variables 'vars("MyVariable").Value = "value from c#" Dim result As DTSExecResult = package.Execute() lblResult.Text = "Package Execution results: {0} " & result.ToString()

All I get is a message 'Failure'.

Does anyone have an example of how to do this?

Your requirement is different and you are doing in wrong way. The mentioned code run the package on the same machine.

http://blogs.msdn.com/michen/archive/2007/03/22/running-ssis-package-programmatically.aspx

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1801974&SiteID=1

|||

ok I've gone for option number 3 on your first link which uses a webservice to execute the package and then I can run the webservice through my asp.net page.

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

This is my webservice - the example taken from that page,

<WebMethod()> _Public Function LaunchPackage( _ByVal sourceTypeAs String, _ByVal sourceLocationAs String, _ByVal packageNameAs String)As Integer'DTSExecResultDim packagePathAs String Dim myPackageAs PackageDim integrationServicesAs New Application' Combine path and file name. packagePath = Path.Combine(sourceLocation, packageName)Select Case sourceTypeCase"file"' Package is stored as a file. ' Add extension if not present.If String.IsNullOrEmpty(Path.GetExtension(packagePath))Then packagePath =String.Concat(packagePath,".dtsx")End If If File.Exists(packagePath)Then myPackage = integrationServices.LoadPackage(packagePath,Nothing)Else Throw New ApplicationException( _"Invalid file location: " & packagePath)End If Case"sql"' Package is stored in MSDB. ' Combine logical path and package name.If integrationServices.ExistsOnSqlServer(packagePath,".",String.Empty,String.Empty)Then myPackage = integrationServices.LoadFromSqlServer( _ packageName,"(local)",String.Empty,String.Empty,Nothing)Else Throw New ApplicationException( _"Invalid package name or location: " & packagePath)End If Case"dts"' Package is managed by SSIS Package Store. ' Default logical paths are File System and MSDB.If integrationServices.ExistsOnDtsServer(packagePath,".")Then myPackage = integrationServices.LoadFromDtsServer(packagePath,"localhost",Nothing)Else Throw New ApplicationException( _"Invalid package name or location: " & packagePath)End If Case Else Throw New ApplicationException( _ "Invalid sourceType argument: valid values are'file', 'sql', and 'dts'.")End Select Return myPackage.Execute()

and my asp.net page

Protected Sub btnBuildDW_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles btnBuildDW.Click ExecutePackage()End Sub Protected Sub ExecutePackage()Dim launchPackageServiceAs New SCCBuildDW.SCCBuildDWDim packageResultAs Integer Try packageResult = launchPackageService.LaunchPackage("file","c:\ssis","Package")Catch exAs Exception' The type of exception returned by a Web service is: ' System.Web.Services.Protocols.SoapException lblResult.Text ="The following exception occurred: " & ex.MessageEnd Try lblResult.Text = packageResult.ToString'CType(packageResult, PackageExecutionResult).ToString ' Console.ReadKey()End Sub Private Enum PackageExecutionResult PackageSucceeded PackageFailed PackageCompleted PackageWasCancelledEnd Enum

I'm getting PackageFailed. The webservice runs and returns 1 - which must be PackageFailed.

Any ideas what I'm doing wrong? The webservice is using windows authentication and the folder the package is in has Everyone Full Control.

|||

I've made some progress. It was failing due to permissions, I've altered these and can now execute a package containing a stored procedure. However my original package runs a stored procedure and then an Analysis Services Task. This still fails - I've checked the user is a member of the role that has access to run the task.

Is there something else I need to do?

|||

I'm still stuck on this - can anyone offer any help?

|||

I have this working now on my local machine. As soon as I move it to the live server I get PackageFailed when I attempt to run it. Has anyone experienced similar problems?

Given the amount of information on the internet I'm beginning to think I'm the only one who wants to execute SSIS in ASP.NET!

|||

i have similar issues too. maybe running ssis dtsx package using web services is not a good idea at all. especially if we run into so many authentication issues.

what about using sql server agent to run ssis package!

|||

I'm now using a stored procedure to execute the package and this works without problems.

Details of my code can be found here -http://forums.asp.net/p/1149972/1873190.aspx#1873190

|||

I'm also having the same problems you have described. I don't have the option of executing from a stored procedure.

I noticed that when I use the <identity impersonate="true"> and specified the username and password, that I got much further, but the package still fails.

I'm guessing that I'll probably end up adjusting the permissions on certain directories and so forth until this works.

|||

Hi all,

this might be useful.

http://www.codeproject.com/useritems/CallSSISFromCSharp.asp?df=100&forumid=309846&exp=0&select=1518271

I tried using this and it works.

How to enhance the performance of a table

hi i have table with around 15 fields and has a pk.
there're some web applications will insert records into it. there're some
backgroup application will query the table.
now, i have around 1000 thousand records, but the there're some locking
behaviour, and even i go query analyzer and do simple query search, i have
the timeout error.
how can i simply improve the performance of it?
thanks!
mullinHi,
how can i simply improve the performance of it?
Create Indexes based on the Where clause of your query. This will definetely
speed up your queries.
How to reduce locks:
1. Create necessary indexes
2. Make the transaction as short as possible.
3. Might be I/O bottle neck.
Whenever your server experiences an I/O bottleneck, the longer it takes
user's transactions to complete.
And the longer they take to complete, the longer locks must be held, which
can lead to other transactions
having to wait for previous locks to be released.
Thanks
Hari
MCDBA
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:uqbTi72JEHA.2576@.TK2MSFTNGP12.phx.gbl...
> hi i have table with around 15 fields and has a pk.
> there're some web applications will insert records into it. there're some
> backgroup application will query the table.
> now, i have around 1000 thousand records, but the there're some locking
> behaviour, and even i go query analyzer and do simple query search, i have
> the timeout error.
> how can i simply improve the performance of it?
> thanks!
> mullin
>

How to enhance the performance of a table

hi i have table with around 15 fields and has a pk.
there're some web applications will insert records into it. there're some
backgroup application will query the table.
now, i have around 1000 thousand records, but the there're some locking
behaviour, and even i go query analyzer and do simple query search, i have
the timeout error.
how can i simply improve the performance of it?
thanks!
mullinHi,
how can i simply improve the performance of it?
Create Indexes based on the Where clause of your query. This will definetely
speed up your queries.
How to reduce locks:
1. Create necessary indexes
2. Make the transaction as short as possible.
3. Might be I/O bottle neck.
Whenever your server experiences an I/O bottleneck, the longer it takes
user's transactions to complete.
And the longer they take to complete, the longer locks must be held, which
can lead to other transactions
having to wait for previous locks to be released.
Thanks
Hari
MCDBA
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:uqbTi72JEHA.2576@.TK2MSFTNGP12.phx.gbl...
> hi i have table with around 15 fields and has a pk.
> there're some web applications will insert records into it. there're some
> backgroup application will query the table.
> now, i have around 1000 thousand records, but the there're some locking
> behaviour, and even i go query analyzer and do simple query search, i have
> the timeout error.
> how can i simply improve the performance of it?
> thanks!
> mullin
>

How to enhance the performance of a table

hi i have table with around 15 fields and has a pk.
there're some web applications will insert records into it. there're some
backgroup application will query the table.
now, i have around 1000 thousand records, but the there're some locking
behaviour, and even i go query analyzer and do simple query search, i have
the timeout error.
how can i simply improve the performance of it?
thanks!
mullin
Hi,
how can i simply improve the performance of it?
Create Indexes based on the Where clause of your query. This will definetely
speed up your queries.
How to reduce locks:
1. Create necessary indexes
2. Make the transaction as short as possible.
3. Might be I/O bottle neck.
Whenever your server experiences an I/O bottleneck, the longer it takes
user's transactions to complete.
And the longer they take to complete, the longer locks must be held, which
can lead to other transactions
having to wait for previous locks to be released.
Thanks
Hari
MCDBA
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:uqbTi72JEHA.2576@.TK2MSFTNGP12.phx.gbl...
> hi i have table with around 15 fields and has a pk.
> there're some web applications will insert records into it. there're some
> backgroup application will query the table.
> now, i have around 1000 thousand records, but the there're some locking
> behaviour, and even i go query analyzer and do simple query search, i have
> the timeout error.
> how can i simply improve the performance of it?
> thanks!
> mullin
>

how to encrypt and decypt as a IUSR with read and write only rights

How to decrypt or encrypt without making user a db_owner. It is for a web application and I do not want make the web user a db_owner. Is there a way to make this work without making the user a db_owner. Currently the user is a db_datareader and db_datawriter.

I created an asymmetric key for encryption by password. I am not using a master key because I want to keep the password seperately on the web server, so a hacker cannot get access to both if database gets hacked.

These are the steps I took when I logged in to SQL server management studio using windows authentication:

CREATE ASYMMETRIC KEY ccnumber WITH ALGORITHM = RSA_512
ENCRYPTION BY PASSWORD = 'password';

INSERT INTO Payments (CreditCardNumber,enc_CreditCardNumber)
values( '458724124',
EncryptByAsymKey(AsymKey_ID('ccnumber'), '458724124') )

SELECT CONVERT(varchar(50), DecryptByAsymKey( AsymKey_Id('ccnumber'), enc_CreditCardNumber, N'password' ))
AS Creditcardnumber , Creditcardnumber
FROM payments where Creditcardnumber = '458724124'

When I use the above select statement it works if I make the user a db_owner but I get null if the user is just db_reader and db_writer.

Is there a way to do encryption without making the user a db_owner?

Yes, you can encrypt without being a db_owner. Note that you should not use asymmetric key encryption for encrypting data. You should use symmetric keys to encrypt data and asymmetric keys to protect other keys or for signing code.

To encrypt and decrypt with an asymmetric key, you need to grant CONTROL permission on the key (VIEW is sufficient for encryption, but for decryption you need CONTROL and knowledge of the password).

For encryption and decryption with a symmetric key, the user must be able to open the symmetric key (see permissions section of this BOL article: http://msdn2.microsoft.com/en-us/library/ms190499.aspx). You may also find the following blog post useful: http://blogs.msdn.com/lcris/archive/2006/01/13/512829.aspx.

Thanks
Laurentiu

sql

How to enable the option of " Create New SQL Server database " from Database Explorer

Hi there

I am working on Visual Web Developer Express Edition 2005. When I right click on database explorer to create an SQL server database then I always find the option " Create New SQL Server database " Disabled.

Can any one tell me how to enable that option please ?

You are probably in the wrong forum for this question, but do you have SQL Server Express installed?

http://msdn.microsoft.com/vstudio/express/sql/

|||Why not download SQL Server Managment Studio Express? If you click the Add Connection option, you can create a database in the dialog that comes up. Just type a filename in the textbox that doesn't exist and you'll be prompted to create it.

How to enable the option of " Create New SQL Server database " from Database Explo

Hi there

I am working on Visual Web Developer Express Edition 2005. When I right click on database explorer to create an SQL server database then I always find the option " Create New SQL Server database " Disabled.

Can any one tell me how to enable that option please ?

You are probably in the wrong forum for this question, but do you have SQL Server Express installed?

http://msdn.microsoft.com/vstudio/express/sql/

|||Why not download SQL Server Managment Studio Express? If you click the Add Connection option, you can create a database in the dialog that comes up. Just type a filename in the textbox that doesn't exist and you'll be prompted to create it.sql

How to dynamically process a Model in a Web App

Hi,

I am a novice at Data Mining realm on SQL Server.

Scenario:

I have created a Time Series model and deployed it into SQL Server. I hope users can see forecast based on the up-to-date data residing in data source rather than the old ones used to train the model. In a addition, the interface provided for users is a .aspx.

Problems:

What ADO APIs should I exploit to dynamically process the model, perform the forecast and retrieve the results.

Any help would be appreciated.

Best Wishes,

Ricky.

You would likely use ADOMD.NET, not ADO.NEt, but the results would essentially be the same. You would likely want to create models on the fly for this solution, much the same way we do for the Excel addins. You can download the addins and use the trace mechanism to see what commands we send to the server.

Essentially, you want to use CREATE SESSION MINING MODEL, INSERT INTO (you can use an input rowset, or an openquery if your data is in a database), and then SELECT PredictTimeSeries(...) to get the forecast.

Using a session model will cause the model to automatically be deleted on disconnection. Note that you will have to turn on the server property to allow session models.

|||

Thanks a lot, Jamie.

Could you give a tutorial or exmaple code to see the deatils?

Regards,

Ricky.

|||The sample here (http://www.sqlserverdatamining.com/DMCommunity/LiveSamples/1866.aspx) creates and trains models dynamically|||

Many Thanks, Jamie

Regards,

Ricky.

|||

mr jamie...

this link seems to be dead

please send in the link again

|||

http://www.sqlserverdatamining.com/DMCommunity/LiveSamples/1866.aspx

|||

hi jamie..

could u look into this thread plz...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1449395&SiteID=1

;m stuck at thiz prb...

i've reinstalled the dm viewer controls.......

now here is wat i read in the readme file as to how to use the dm viewer controls:

In the Winform designer, right click on the Toolbox and select 'Choose Items...' menu item. Hit the Brows button and select file 'Microsoft.AnalysisServices.Viewers.dll'. Hit the OK button to add all the viewer controls to your toolbox.

the prob is tat i can't find this file:'Microsoft.AnalysisServices.Viewers.dll'

do u think i've gone wrong somewhr in the installation of the viewer controls?

or is there somethin else that i must do?

how to dynamically create report in web app?

Hello,
I am rather new to report service. What I want to achieve is prvide a web
page and let users to choose what fields they want to see, what table they
want to query and what formats they want to apply.
Is it something achievable? Would someone give me some tutorial or hints?
Many Thanks
--
hello, please helpAlthough possible it is not trivial. You need to know the RDL specification
(go to MSDN.microsoft.com and search on RDL specification, there are several
articles). Then there is the issue that this is a server based product, you
cannot change the RDL on the fly. You have to publish the RDL. With RS 2005
you can use the new controls and give the control the RDL and the dataset
(in local mode you do not even need the server). These controls come with VS
2005 (Not with SQL Server). So, check out the spec and see if this is really
something you want to tackle. Depending on the complexity you might be
better off to use XML and XSL instead.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jerry.xuddd" <jerryxuddd@.discussions.microsoft.com> wrote in message
news:78300BE1-38D8-4491-997D-2C4C0EC98AFE@.microsoft.com...
> Hello,
> I am rather new to report service. What I want to achieve is prvide a web
> page and let users to choose what fields they want to see, what table they
> want to query and what formats they want to apply.
> Is it something achievable? Would someone give me some tutorial or hints?
> Many Thanks
> --
> hello, please help

How to drilldown in SOAP-rendered reports

Hi,
we tried the SQLRS-ReportViewer from the
We changed the necessary things to get it to work with the new 2005 web
services. But now we wonder how we can handle drilldowns. In the 2000
versions of the web services there was an url-parameter showhidetoggle which
seemed to be the candidate. But we didn't get it to work. But now even this
parameter seems to have disappeared, and we are not able to find helping
documentaion.
Regards,
RalphThere is a ToggleItem API in the new ReportExecution web service. The
problem with it though is that it takes the id of the item to be expanded
but these IDs are known only when the report is rendered. It's like catch
22... You need the id to call ToggleItem but you cannot easily get it. If
you do some tracing of the incoming SOAP traffic, you will see what I mean.
So, to get this working, you need to parse the report HTML output to find
the image id which may look as follows
<IMG ID="7" BORDER="0"....
Then, when the user clicks you need to invoke ToggleItem passing the image
identifier. If this sounds like a brain surgery, use the VS.NET 2005 report
viewers and be done with it. They handle interactivity for you.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"Ralph Watermann" <Ralph.Watermann@.webtelligence.net> wrote in message
news:%23W0uysb6FHA.268@.TK2MSFTNGP10.phx.gbl...
> Hi,
> we tried the SQLRS-ReportViewer from the
> We changed the necessary things to get it to work with the new 2005 web
> services. But now we wonder how we can handle drilldowns. In the 2000
> versions of the web services there was an url-parameter showhidetoggle
> which seemed to be the candidate. But we didn't get it to work. But now
> even this parameter seems to have disappeared, and we are not able to find
> helping documentaion.
> Regards,
> Ralph
>|||Hmm, we'd really like to use the report viewer control. But we need some
control on how the parameters are presented. How many in a row, logically
grouped together in a group box,... If we now use a report viewer control on
an asp.net page, i think the drilldown links will go to the old server-url
and our parameters disappear?
On the other hand we will have to use drill-through-links not directly to
another report, but trough an url and put together the path and parameters
on our own. And this will be a very long string where a lot of mistakes can
be done...
Is this the only way to get control over parameter layout?
"Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> schrieb im Newsbeitrag
news:eaJw4ge6FHA.2888@.tk2msftngp13.phx.gbl...
> There is a ToggleItem API in the new ReportExecution web service. The
> problem with it though is that it takes the id of the item to be expanded
> but these IDs are known only when the report is rendered. It's like catch
> 22... You need the id to call ToggleItem but you cannot easily get it. If
> you do some tracing of the incoming SOAP traffic, you will see what I
> mean. So, to get this working, you need to parse the report HTML output to
> find the image id which may look as follows
> <IMG ID="7" BORDER="0"....
> Then, when the user clicks you need to invoke ToggleItem passing the image
> identifier. If this sounds like a brain surgery, use the VS.NET 2005
> report viewers and be done with it. They handle interactivity for you.
> --
> HTH,
> ---
> Teo Lachev, MVP, MCSD, MCT
> "Microsoft Reporting Services in Action"
> "Applied Microsoft Analysis Services 2005"
> Home page and blog: http://www.prologika.com/
> ---
> "Ralph Watermann" <Ralph.Watermann@.webtelligence.net> wrote in message
> news:%23W0uysb6FHA.268@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> we tried the SQLRS-ReportViewer from the
>> We changed the necessary things to get it to work with the new 2005 web
>> services. But now we wonder how we can handle drilldowns. In the 2000
>> versions of the web services there was an url-parameter showhidetoggle
>> which seemed to be the candidate. But we didn't get it to work. But now
>> even this parameter seems to have disappeared, and we are not able to
>> find helping documentaion.
>> Regards,
>> Ralph
>>
>|||So, just replace the toolbar then if you need more control over the Report
Viewer toolbar.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"Ralph Watermann" <We.Want@.NoSpam.de> wrote in message
news:OcKGmPf6FHA.1276@.TK2MSFTNGP09.phx.gbl...
> Hmm, we'd really like to use the report viewer control. But we need some
> control on how the parameters are presented. How many in a row, logically
> grouped together in a group box,... If we now use a report viewer control
> on an asp.net page, i think the drilldown links will go to the old
> server-url and our parameters disappear?
> On the other hand we will have to use drill-through-links not directly to
> another report, but trough an url and put together the path and parameters
> on our own. And this will be a very long string where a lot of mistakes
> can be done...
> Is this the only way to get control over parameter layout?
> "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> schrieb im
> Newsbeitrag news:eaJw4ge6FHA.2888@.tk2msftngp13.phx.gbl...
>> There is a ToggleItem API in the new ReportExecution web service. The
>> problem with it though is that it takes the id of the item to be expanded
>> but these IDs are known only when the report is rendered. It's like catch
>> 22... You need the id to call ToggleItem but you cannot easily get it. If
>> you do some tracing of the incoming SOAP traffic, you will see what I
>> mean. So, to get this working, you need to parse the report HTML output
>> to find the image id which may look as follows
>> <IMG ID="7" BORDER="0"....
>> Then, when the user clicks you need to invoke ToggleItem passing the
>> image identifier. If this sounds like a brain surgery, use the VS.NET
>> 2005 report viewers and be done with it. They handle interactivity for
>> you.
>> --
>> HTH,
>> ---
>> Teo Lachev, MVP, MCSD, MCT
>> "Microsoft Reporting Services in Action"
>> "Applied Microsoft Analysis Services 2005"
>> Home page and blog: http://www.prologika.com/
>> ---
>> "Ralph Watermann" <Ralph.Watermann@.webtelligence.net> wrote in message
>> news:%23W0uysb6FHA.268@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> we tried the SQLRS-ReportViewer from the
>> We changed the necessary things to get it to work with the new 2005 web
>> services. But now we wonder how we can handle drilldowns. In the 2000
>> versions of the web services there was an url-parameter showhidetoggle
>> which seemed to be the candidate. But we didn't get it to work. But now
>> even this parameter seems to have disappeared, and we are not able to
>> find helping documentaion.
>> Regards,
>> Ralph
>>
>>
>|||Teo
Could you give me an example on how to call the ToggleItem method? Do I need
to call it together with LoadReport, SetExecutionParameters and Render? If
yes, in what order do I call it? I have experimented calling it in all kinds
of order but haven't been able to get it to work ...
BTW, I think I have found a better way to find the ID of the item that is to
be toggled - you can get it from the query string, like:
Request.QueryString["rs:ShowHideToggle"].
Any help would be much appreciated
mike
"Teo Lachev [MVP]" wrote:
> So, just replace the toolbar then if you need more control over the Report
> Viewer toolbar.
> --
> HTH,
> ---
> Teo Lachev, MVP, MCSD, MCT
> "Microsoft Reporting Services in Action"
> "Applied Microsoft Analysis Services 2005"
> Home page and blog: http://www.prologika.com/
> ---
> "Ralph Watermann" <We.Want@.NoSpam.de> wrote in message
> news:OcKGmPf6FHA.1276@.TK2MSFTNGP09.phx.gbl...
> > Hmm, we'd really like to use the report viewer control. But we need some
> > control on how the parameters are presented. How many in a row, logically
> > grouped together in a group box,... If we now use a report viewer control
> > on an asp.net page, i think the drilldown links will go to the old
> > server-url and our parameters disappear?
> >
> > On the other hand we will have to use drill-through-links not directly to
> > another report, but trough an url and put together the path and parameters
> > on our own. And this will be a very long string where a lot of mistakes
> > can be done...
> >
> > Is this the only way to get control over parameter layout?
> >
> > "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> schrieb im
> > Newsbeitrag news:eaJw4ge6FHA.2888@.tk2msftngp13.phx.gbl...
> >> There is a ToggleItem API in the new ReportExecution web service. The
> >> problem with it though is that it takes the id of the item to be expanded
> >> but these IDs are known only when the report is rendered. It's like catch
> >> 22... You need the id to call ToggleItem but you cannot easily get it. If
> >> you do some tracing of the incoming SOAP traffic, you will see what I
> >> mean. So, to get this working, you need to parse the report HTML output
> >> to find the image id which may look as follows
> >> <IMG ID="7" BORDER="0"....
> >>
> >> Then, when the user clicks you need to invoke ToggleItem passing the
> >> image identifier. If this sounds like a brain surgery, use the VS.NET
> >> 2005 report viewers and be done with it. They handle interactivity for
> >> you.
> >>
> >> --
> >> HTH,
> >> ---
> >> Teo Lachev, MVP, MCSD, MCT
> >> "Microsoft Reporting Services in Action"
> >> "Applied Microsoft Analysis Services 2005"
> >> Home page and blog: http://www.prologika.com/
> >> ---
> >> "Ralph Watermann" <Ralph.Watermann@.webtelligence.net> wrote in message
> >> news:%23W0uysb6FHA.268@.TK2MSFTNGP10.phx.gbl...
> >> Hi,
> >>
> >> we tried the SQLRS-ReportViewer from the
> >>
> >> We changed the necessary things to get it to work with the new 2005 web
> >> services. But now we wonder how we can handle drilldowns. In the 2000
> >> versions of the web services there was an url-parameter showhidetoggle
> >> which seemed to be the candidate. But we didn't get it to work. But now
> >> even this parameter seems to have disappeared, and we are not able to
> >> find helping documentaion.
> >>
> >> Regards,
> >> Ralph
> >>
> >>
> >>
> >>
> >
> >
>
>|||Mike,
From what I have found, you need to actually call the Render method twice.
I have not been able to get reproducible results using a single call to the
render method. Here is some code that works for me:
byte[] result = null;
string reportPath = "/Accounting/Monthly Revenue Projection
DrillDown";
string format = "IMAGE";
string historyID = null;
string devInfo =@."<DeviceInfo><OutputFormat>EMF</OutputFormat><StartPage>0</StartPage></DeviceInfo>";
ParameterValue[] parameters = new ParameterValue[1];
parameters[0] = new ParameterValue();
parameters[0].Name = "BillCycle";
parameters[0].Value = "305";
string encoding;
string mimeType;
string extension;
Warning[] warnings = null;
string[] streamIDs = null;
try
{
ExecutionInfo execInfo = new ExecutionInfo();
ExecutionHeader execHeader = new ExecutionHeader();
rs.ExecutionHeaderValue = execHeader;
execInfo = rs.LoadReport(reportPath, historyID);
rs.SetExecutionParameters(parameters, "en-us");
String SessionId = rs.ExecutionHeaderValue.ExecutionID;
System.Diagnostics.Debug.WriteLine("SessionID: {0}",
rs.ExecutionHeaderValue.ExecutionID);
result = rs.Render(format, devInfo, out extension, out encoding,
out mimeType, out warnings, out streamIDs);
System.Diagnostics.Debug.WriteLine("Stream IDs: " +
streamIDs.Length);
#region Apply Toggle Drilldown
bool resultToggle = false;
resultToggle = rs.ToggleItem("1236");
result = rs.Render(format, devInfo, out extension, out encoding,
out mimeType, out warnings, out streamIDs);
#endregion
execInfo = rs.GetExecutionInfo();
System.Diagnostics.Debug.WriteLine(string.Format("Execution date
and time: {0}", execInfo.ExecutionDateTime));
}
catch (SoapException e)
{
System.Diagnostics.Debug.WriteLine(e.Detail.OuterXml);
}
Jeff
"mikel" wrote:
> Teo
> Could you give me an example on how to call the ToggleItem method? Do I need
> to call it together with LoadReport, SetExecutionParameters and Render? If
> yes, in what order do I call it? I have experimented calling it in all kinds
> of order but haven't been able to get it to work ...
> BTW, I think I have found a better way to find the ID of the item that is to
> be toggled - you can get it from the query string, like:
> Request.QueryString["rs:ShowHideToggle"].
> Any help would be much appreciated
> mike
> "Teo Lachev [MVP]" wrote:
> > So, just replace the toolbar then if you need more control over the Report
> > Viewer toolbar.
> >
> > --
> > HTH,
> > ---
> > Teo Lachev, MVP, MCSD, MCT
> > "Microsoft Reporting Services in Action"
> > "Applied Microsoft Analysis Services 2005"
> > Home page and blog: http://www.prologika.com/
> > ---
> > "Ralph Watermann" <We.Want@.NoSpam.de> wrote in message
> > news:OcKGmPf6FHA.1276@.TK2MSFTNGP09.phx.gbl...
> > > Hmm, we'd really like to use the report viewer control. But we need some
> > > control on how the parameters are presented. How many in a row, logically
> > > grouped together in a group box,... If we now use a report viewer control
> > > on an asp.net page, i think the drilldown links will go to the old
> > > server-url and our parameters disappear?
> > >
> > > On the other hand we will have to use drill-through-links not directly to
> > > another report, but trough an url and put together the path and parameters
> > > on our own. And this will be a very long string where a lot of mistakes
> > > can be done...
> > >
> > > Is this the only way to get control over parameter layout?
> > >
> > > "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> schrieb im
> > > Newsbeitrag news:eaJw4ge6FHA.2888@.tk2msftngp13.phx.gbl...
> > >> There is a ToggleItem API in the new ReportExecution web service. The
> > >> problem with it though is that it takes the id of the item to be expanded
> > >> but these IDs are known only when the report is rendered. It's like catch
> > >> 22... You need the id to call ToggleItem but you cannot easily get it. If
> > >> you do some tracing of the incoming SOAP traffic, you will see what I
> > >> mean. So, to get this working, you need to parse the report HTML output
> > >> to find the image id which may look as follows
> > >> <IMG ID="7" BORDER="0"....
> > >>
> > >> Then, when the user clicks you need to invoke ToggleItem passing the
> > >> image identifier. If this sounds like a brain surgery, use the VS.NET
> > >> 2005 report viewers and be done with it. They handle interactivity for
> > >> you.
> > >>
> > >> --
> > >> HTH,
> > >> ---
> > >> Teo Lachev, MVP, MCSD, MCT
> > >> "Microsoft Reporting Services in Action"
> > >> "Applied Microsoft Analysis Services 2005"
> > >> Home page and blog: http://www.prologika.com/
> > >> ---
> > >> "Ralph Watermann" <Ralph.Watermann@.webtelligence.net> wrote in message
> > >> news:%23W0uysb6FHA.268@.TK2MSFTNGP10.phx.gbl...
> > >> Hi,
> > >>
> > >> we tried the SQLRS-ReportViewer from the
> > >>
> > >> We changed the necessary things to get it to work with the new 2005 web
> > >> services. But now we wonder how we can handle drilldowns. In the 2000
> > >> versions of the web services there was an url-parameter showhidetoggle
> > >> which seemed to be the candidate. But we didn't get it to work. But now
> > >> even this parameter seems to have disappeared, and we are not able to
> > >> find helping documentaion.
> > >>
> > >> Regards,
> > >> Ralph
> > >>
> > >>
> > >>
> > >>
> > >
> > >
> >
> >
> >|||Jeff
Thanks for your reply.
I did manage to get ToggleItem to work for HTML4.0 output with calling
Render only once (that is if you don't count the first call to Render to
display the report initially).
I noticed that in your example you were outputing your report to image. So,
since there is no ability for the user to toggle/untoggle items by clicking
on the report, I can see how would have to call Render twice. ToggleItem
needs to be called on an existing execution and will in fact fail if the
execution expires.
What I am having problems with now is trying to export the report to PDF in
its toggled state. No matter what I try (including calling Render twice), it
always comes back with all toggle items collapsed. Do you have any
suggestions on that?
"jgeerwm" wrote:
> Mike,
> From what I have found, you need to actually call the Render method twice.
> I have not been able to get reproducible results using a single call to the
> render method. Here is some code that works for me:
> byte[] result = null;
> string reportPath = "/Accounting/Monthly Revenue Projection
> DrillDown";
> string format = "IMAGE";
> string historyID = null;
> string devInfo => @."<DeviceInfo><OutputFormat>EMF</OutputFormat><StartPage>0</StartPage></DeviceInfo>";
> ParameterValue[] parameters = new ParameterValue[1];
> parameters[0] = new ParameterValue();
> parameters[0].Name = "BillCycle";
> parameters[0].Value = "305";
> string encoding;
> string mimeType;
> string extension;
> Warning[] warnings = null;
> string[] streamIDs = null;
> try
> {
> ExecutionInfo execInfo = new ExecutionInfo();
> ExecutionHeader execHeader = new ExecutionHeader();
> rs.ExecutionHeaderValue = execHeader;
> execInfo = rs.LoadReport(reportPath, historyID);
> rs.SetExecutionParameters(parameters, "en-us");
> String SessionId = rs.ExecutionHeaderValue.ExecutionID;
> System.Diagnostics.Debug.WriteLine("SessionID: {0}",
> rs.ExecutionHeaderValue.ExecutionID);
> result = rs.Render(format, devInfo, out extension, out encoding,
> out mimeType, out warnings, out streamIDs);
> System.Diagnostics.Debug.WriteLine("Stream IDs: " +
> streamIDs.Length);
> #region Apply Toggle Drilldown
> bool resultToggle = false;
> resultToggle = rs.ToggleItem("1236");
> result = rs.Render(format, devInfo, out extension, out encoding,
> out mimeType, out warnings, out streamIDs);
> #endregion
> execInfo = rs.GetExecutionInfo();
> System.Diagnostics.Debug.WriteLine(string.Format("Execution date
> and time: {0}", execInfo.ExecutionDateTime));
> }
> catch (SoapException e)
> {
> System.Diagnostics.Debug.WriteLine(e.Detail.OuterXml);
> }
> Jeff
> "mikel" wrote:
> > Teo
> >
> > Could you give me an example on how to call the ToggleItem method? Do I need
> > to call it together with LoadReport, SetExecutionParameters and Render? If
> > yes, in what order do I call it? I have experimented calling it in all kinds
> > of order but haven't been able to get it to work ...
> >
> > BTW, I think I have found a better way to find the ID of the item that is to
> > be toggled - you can get it from the query string, like:
> > Request.QueryString["rs:ShowHideToggle"].
> >
> > Any help would be much appreciated
> >
> > mike
> >
> > "Teo Lachev [MVP]" wrote:
> >
> > > So, just replace the toolbar then if you need more control over the Report
> > > Viewer toolbar.
> > >
> > > --
> > > HTH,
> > > ---
> > > Teo Lachev, MVP, MCSD, MCT
> > > "Microsoft Reporting Services in Action"
> > > "Applied Microsoft Analysis Services 2005"
> > > Home page and blog: http://www.prologika.com/
> > > ---
> > > "Ralph Watermann" <We.Want@.NoSpam.de> wrote in message
> > > news:OcKGmPf6FHA.1276@.TK2MSFTNGP09.phx.gbl...
> > > > Hmm, we'd really like to use the report viewer control. But we need some
> > > > control on how the parameters are presented. How many in a row, logically
> > > > grouped together in a group box,... If we now use a report viewer control
> > > > on an asp.net page, i think the drilldown links will go to the old
> > > > server-url and our parameters disappear?
> > > >
> > > > On the other hand we will have to use drill-through-links not directly to
> > > > another report, but trough an url and put together the path and parameters
> > > > on our own. And this will be a very long string where a lot of mistakes
> > > > can be done...
> > > >
> > > > Is this the only way to get control over parameter layout?
> > > >
> > > > "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> schrieb im
> > > > Newsbeitrag news:eaJw4ge6FHA.2888@.tk2msftngp13.phx.gbl...
> > > >> There is a ToggleItem API in the new ReportExecution web service. The
> > > >> problem with it though is that it takes the id of the item to be expanded
> > > >> but these IDs are known only when the report is rendered. It's like catch
> > > >> 22... You need the id to call ToggleItem but you cannot easily get it. If
> > > >> you do some tracing of the incoming SOAP traffic, you will see what I
> > > >> mean. So, to get this working, you need to parse the report HTML output
> > > >> to find the image id which may look as follows
> > > >> <IMG ID="7" BORDER="0"....
> > > >>
> > > >> Then, when the user clicks you need to invoke ToggleItem passing the
> > > >> image identifier. If this sounds like a brain surgery, use the VS.NET
> > > >> 2005 report viewers and be done with it. They handle interactivity for
> > > >> you.
> > > >>
> > > >> --
> > > >> HTH,
> > > >> ---
> > > >> Teo Lachev, MVP, MCSD, MCT
> > > >> "Microsoft Reporting Services in Action"
> > > >> "Applied Microsoft Analysis Services 2005"
> > > >> Home page and blog: http://www.prologika.com/
> > > >> ---
> > > >> "Ralph Watermann" <Ralph.Watermann@.webtelligence.net> wrote in message
> > > >> news:%23W0uysb6FHA.268@.TK2MSFTNGP10.phx.gbl...
> > > >> Hi,
> > > >>
> > > >> we tried the SQLRS-ReportViewer from the
> > > >>
> > > >> We changed the necessary things to get it to work with the new 2005 web
> > > >> services. But now we wonder how we can handle drilldowns. In the 2000
> > > >> versions of the web services there was an url-parameter showhidetoggle
> > > >> which seemed to be the candidate. But we didn't get it to work. But now
> > > >> even this parameter seems to have disappeared, and we are not able to
> > > >> find helping documentaion.
> > > >>
> > > >> Regards,
> > > >> Ralph
> > > >>
> > > >>
> > > >>
> > > >>
> > > >
> > > >
> > >
> > >
> > >|||OK. I have figured it out.
For anyone else that may be experiencing the same problem. If you render
your report as HTML4.0, then you toggle some report items, then if you want
to export the report to another format like PDF or image in its toggled
state, you need to:
1. Reuse the reference to ExecutionService that you have preserved in let's
say Session between calls
ReportExecution.ReportExecutionService rsExec =(ReportExecutionService)Session["rs"];
2. Set report parameters that you have also preserved between calls
rsExec.SetExecutionParameters((ReportExecution.ParameterValue[])
Session("reportParameterValues"], "en-us");
3. DO NOT call rsExec.ToggleItem - you are reusing the original report
execution, and if you call ToggleItem again this will have the effect of
untoggling the items that you have previously toggled
4. Set up device info and call Render as normal - Jeff's post 2 posts back
is a good example of how to.
"mikel" wrote:
> Jeff
> Thanks for your reply.
> I did manage to get ToggleItem to work for HTML4.0 output with calling
> Render only once (that is if you don't count the first call to Render to
> display the report initially).
> I noticed that in your example you were outputing your report to image. So,
> since there is no ability for the user to toggle/untoggle items by clicking
> on the report, I can see how would have to call Render twice. ToggleItem
> needs to be called on an existing execution and will in fact fail if the
> execution expires.
> What I am having problems with now is trying to export the report to PDF in
> its toggled state. No matter what I try (including calling Render twice), it
> always comes back with all toggle items collapsed. Do you have any
> suggestions on that?
>
> "jgeerwm" wrote:
> > Mike,
> >
> > From what I have found, you need to actually call the Render method twice.
> > I have not been able to get reproducible results using a single call to the
> > render method. Here is some code that works for me:
> >
> > byte[] result = null;
> >
> > string reportPath = "/Accounting/Monthly Revenue Projection
> > DrillDown";
> > string format = "IMAGE";
> > string historyID = null;
> > string devInfo => > @."<DeviceInfo><OutputFormat>EMF</OutputFormat><StartPage>0</StartPage></DeviceInfo>";
> >
> > ParameterValue[] parameters = new ParameterValue[1];
> > parameters[0] = new ParameterValue();
> > parameters[0].Name = "BillCycle";
> > parameters[0].Value = "305";
> >
> > string encoding;
> > string mimeType;
> > string extension;
> > Warning[] warnings = null;
> > string[] streamIDs = null;
> >
> > try
> > {
> > ExecutionInfo execInfo = new ExecutionInfo();
> > ExecutionHeader execHeader = new ExecutionHeader();
> >
> > rs.ExecutionHeaderValue = execHeader;
> >
> > execInfo = rs.LoadReport(reportPath, historyID);
> >
> > rs.SetExecutionParameters(parameters, "en-us");
> > String SessionId = rs.ExecutionHeaderValue.ExecutionID;
> >
> > System.Diagnostics.Debug.WriteLine("SessionID: {0}",
> > rs.ExecutionHeaderValue.ExecutionID);
> >
> > result = rs.Render(format, devInfo, out extension, out encoding,
> > out mimeType, out warnings, out streamIDs);
> >
> > System.Diagnostics.Debug.WriteLine("Stream IDs: " +
> > streamIDs.Length);
> >
> > #region Apply Toggle Drilldown
> >
> > bool resultToggle = false;
> >
> > resultToggle = rs.ToggleItem("1236");
> > result = rs.Render(format, devInfo, out extension, out encoding,
> > out mimeType, out warnings, out streamIDs);
> >
> > #endregion
> >
> > execInfo = rs.GetExecutionInfo();
> > System.Diagnostics.Debug.WriteLine(string.Format("Execution date
> > and time: {0}", execInfo.ExecutionDateTime));
> >
> > }
> > catch (SoapException e)
> > {
> > System.Diagnostics.Debug.WriteLine(e.Detail.OuterXml);
> > }
> >
> > Jeff
> >
> > "mikel" wrote:
> >
> > > Teo
> > >
> > > Could you give me an example on how to call the ToggleItem method? Do I need
> > > to call it together with LoadReport, SetExecutionParameters and Render? If
> > > yes, in what order do I call it? I have experimented calling it in all kinds
> > > of order but haven't been able to get it to work ...
> > >
> > > BTW, I think I have found a better way to find the ID of the item that is to
> > > be toggled - you can get it from the query string, like:
> > > Request.QueryString["rs:ShowHideToggle"].
> > >
> > > Any help would be much appreciated
> > >
> > > mike
> > >
> > > "Teo Lachev [MVP]" wrote:
> > >
> > > > So, just replace the toolbar then if you need more control over the Report
> > > > Viewer toolbar.
> > > >
> > > > --
> > > > HTH,
> > > > ---
> > > > Teo Lachev, MVP, MCSD, MCT
> > > > "Microsoft Reporting Services in Action"
> > > > "Applied Microsoft Analysis Services 2005"
> > > > Home page and blog: http://www.prologika.com/
> > > > ---
> > > > "Ralph Watermann" <We.Want@.NoSpam.de> wrote in message
> > > > news:OcKGmPf6FHA.1276@.TK2MSFTNGP09.phx.gbl...
> > > > > Hmm, we'd really like to use the report viewer control. But we need some
> > > > > control on how the parameters are presented. How many in a row, logically
> > > > > grouped together in a group box,... If we now use a report viewer control
> > > > > on an asp.net page, i think the drilldown links will go to the old
> > > > > server-url and our parameters disappear?
> > > > >
> > > > > On the other hand we will have to use drill-through-links not directly to
> > > > > another report, but trough an url and put together the path and parameters
> > > > > on our own. And this will be a very long string where a lot of mistakes
> > > > > can be done...
> > > > >
> > > > > Is this the only way to get control over parameter layout?
> > > > >
> > > > > "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> schrieb im
> > > > > Newsbeitrag news:eaJw4ge6FHA.2888@.tk2msftngp13.phx.gbl...
> > > > >> There is a ToggleItem API in the new ReportExecution web service. The
> > > > >> problem with it though is that it takes the id of the item to be expanded
> > > > >> but these IDs are known only when the report is rendered. It's like catch
> > > > >> 22... You need the id to call ToggleItem but you cannot easily get it. If
> > > > >> you do some tracing of the incoming SOAP traffic, you will see what I
> > > > >> mean. So, to get this working, you need to parse the report HTML output
> > > > >> to find the image id which may look as follows
> > > > >> <IMG ID="7" BORDER="0"....
> > > > >>
> > > > >> Then, when the user clicks you need to invoke ToggleItem passing the
> > > > >> image identifier. If this sounds like a brain surgery, use the VS.NET
> > > > >> 2005 report viewers and be done with it. They handle interactivity for
> > > > >> you.
> > > > >>
> > > > >> --
> > > > >> HTH,
> > > > >> ---
> > > > >> Teo Lachev, MVP, MCSD, MCT
> > > > >> "Microsoft Reporting Services in Action"
> > > > >> "Applied Microsoft Analysis Services 2005"
> > > > >> Home page and blog: http://www.prologika.com/
> > > > >> ---
> > > > >> "Ralph Watermann" <Ralph.Watermann@.webtelligence.net> wrote in message
> > > > >> news:%23W0uysb6FHA.268@.TK2MSFTNGP10.phx.gbl...
> > > > >> Hi,
> > > > >>
> > > > >> we tried the SQLRS-ReportViewer from the
> > > > >>
> > > > >> We changed the necessary things to get it to work with the new 2005 web
> > > > >> services. But now we wonder how we can handle drilldowns. In the 2000
> > > > >> versions of the web services there was an url-parameter showhidetoggle
> > > > >> which seemed to be the candidate. But we didn't get it to work. But now
> > > > >> even this parameter seems to have disappeared, and we are not able to
> > > > >> find helping documentaion.
> > > > >>
> > > > >> Regards,
> > > > >> Ralph
> > > > >>
> > > > >>
> > > > >>
> > > > >>
> > > > >
> > > > >
> > > >
> > > >
> > > >

How to download a file from SQL Server in my Web APP

Hello people,

Do you know how can do for downloading a file stored in a database?. I'm using a table with a FILE field for storing the file.

I know i have to create a special aspx page for downloading, that receives parameters to locate the proper record in the table and then retrieve the file in memory to start downloading.

I have done this with file located at specific folders but not a database's field.

Another thing... the file may be big.

Dou you have any idea about retrieving from sql and sending the file back to the final user?

I really appreciate your support.

Larry.Here's 2 articles, the first is for smaller files, the second is more complicated but better for larger files:

http://support.microsoft.com/kb/316887

http://support.microsoft.com/default.aspx?scid=kb;en-us;317043

How to do this ?

I want to do a win app or web app that has many reports, I do not want to use CR Service, I want to try SQL Server 2005 Reports, please is anyone who can tell me how to do this, or can send me any link telling me anything in this directon ?

I really appreciate your help !

Sincerely

The first place to start is the Microsoft site.

http://www.microsoft.com/sql/technologies/reporting/default.mspx

|||Hi,
Thnx Brad
Can u suggest me any book speaks about creating reports in VS.NET (2003 or 2005) using SQL Report Services (SQL 2000 or 2005) .
I value your help!

Best regards|||For RS 2000 the book "Microsoft Reporting Services in Action" by Teo Lachev is very good.

How to do paging when there are over 80 000 rows in table?

I have one table in my db which contains some 80 000 rows of data. I want to list the rows in a web page so that one page has 200 rows. I also want to put the first,previous,next and last buttons into the web page so that the users can navigate between the pages/rows nicely.

How can i do this with SQL-Server? I dont want to read all the rows because it takes too long (20 seconds) and uses too much memory.

Do i have to do it like this?
SELECT TOP 200 * FROM TABLE WHERE ID>0 (First page)
SELECT TOP 200 * FROM TABLE WHERE ID>200 (Second page)
SELECT TOP 200 * FROM TABLE WHERE ID>400 (Third page)
... AND SO ON ...

ID = Primary key field (identity insert 1, +1)
(Of course the ID values are different than 0,200,400,... if there are deleted rows in a table.)Only 20 seconds for 80,000 rows of data in a web page - not bad. If you can use a query like select top 200 * from table where id > ?, then use a stored procedure with n as a parameter and use that to multiply by 200. As you progress through the web site, for next just add 1 and for previous subtract 1 from the value you submit to the stored procedure. You could use 1 asp page to handle this functionality. Normally, you are not this lucky and have to use remote scripting to handle large recordsets to return x rows to a web page.

Good luck.