Packaging Business Intelligence Report project for deploymnet

Hi Guys,

I created my report project, now I build it and deploy it and it works fine.

In my web application project, I created an aspx page with reportViewer control and it works fine too. I publish my website for deployment and works fine. I created a web setup project and add my web application project to it and it works fine. It installs and create the virtual directory in wwwroot (Default WebSite) and it run the script on database too and create all tables and sp.

NowHow Do I deploy mey reports on the server as part of the installaton package. Can I do that or it has to be done manually by going to report server and create a new folder and add a DataSource and upload the *.rdl files...?

1 More thing, in my myReport.aspx page where I have the reportviewer control, I have the <ServerReport ReportServerUrl="http://localhost/reportserver/Intranet" Server Path="/MyReports/ProductsReports" />

Well I think this is hard coding as it might be different on the destination server where tha pllication is going to be deployed.

So is it correct way of doing it that in code behind in page load I set these values ( reportViewer1.ServerReport.ReportServerUrl = "..."; ) and I get the value from webconfig file. I have added the url in the web.config file and all my reports use that report server url.

Thanks for your help and suggestions in advance,

Regards,
Mehdi

HI, Mehdi:

I hope this article can help:

Deploying Reports and ReportViewer Controls

http://msdn2.microsoft.com/en-us/library/ms251723(VS.80).aspx

If i misunderstand you about your question, please feel free to correct me and i will try to help you with more information.

I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be of assistance

|||

hi,

i to got struct. i have same problem as described above. i have a web setup of web project that consist of a form that uses report viewer whose report server url is read from web.config. i have a sql reporting services report project which consist of reports. when comes to installation on the client side i can carry web setup of web project but when come to report server project i don't want to carry sourse code to deploy. can it be possible to make websetup of report server project if yes plz reply how to make the setup else say the alternative methods so that i can install at the client side with integration issuess my email id isabdulhaseeb_1201@.yahoo.com. waiting for ur reply. the link which u have provied could not help me a lot, so dont mind plz explain it in your way

Packaging Business Intelligence Report project for deploymnet

Hi Guys,

I created my report project, now I build it and deploy it and it works fine.

In my web application project, I created an aspx page with reportViewer control and it works fine too. I publish my website for deployment and works fine. I created a web setup project and add my web application project to it and it works fine. It installs and create the virtual directory in wwwroot (Default WebSite) and it run the script on database too and create all tables and sp.

NowHow Do I deploy mey reports on the server as part of the installaton package. Can I do that or it has to be done manually by going to report server and create a new folder and add a DataSource and upload the *.rdl files...?

1 More thing, in my myReport.aspx page where I have the reportviewer control, I have the <ServerReport ReportServerUrl="http://localhost/reportserver/Intranet" Server Path="/MyReports/ProductsReports" />

Well I think this is hard coding as it might be different on the destination server where tha pllication is going to be deployed.

So is it correct way of doing it that in code behind in page load I set these values ( reportViewer1.ServerReport.ReportServerUrl = "..."; ) and I get the value from webconfig file. I have added the url in the web.config file and all my reports use that report server url.

Thanks for your help and suggestions in advance,

Regards,
Mehdi

Hi Mehdi,

We use this tool for deploying the reports to report server, its easy to use and solved our problem,

Reporting Services Scripter -http://www.sqldbatips.com/showarticle.asp?ID=62

Overview

Reporting Services Scripter is a .NET Windows Forms application that enables scripting and transfer of all Microsoft SQL Server Reporting Services catalog items to aid in transferring them from one server to another. It can also be used to easily move items on mass from one Reporting Services folder to another on the same server. Depending on the scripting options chosen, Reporting Services Scripter can also transfer all catalog item properties such as Descriptions, History options, Execution options (including report specific and shared schedules), Subscriptions (normal and data driven) and server side report parameters.

Not sure whetehr you can use the command script file with the installer or not, but having seprate deployment script for reports is better as you may have different web and database servers (as in our case).

Now regarding hardcoding the ReportServerUrl and report path you are right its not good practice to do that, web.config file should be used for storing these values so that you can update them on web server itself.

Mehdi6002:

it installs and create the virtual directory in wwwroot (Default WebSite) and it run the script on database too and create all tables and sp.

Can you tell how you run the database script through installer? :)

|||

Hello akjoshi,

Thanks for your reply. Would you just add a key to your web config file in the <appSettings> for the reports folder and reportServerUrl or there is a specific place that you have to include these, like in <httpHandlers> or < buildProviders> or ...

at the moment I just have 2 keys in <appSettings>

<add key="ReportServerUrl" value="http://..." />
<add key="ReportFolder" value="/ABC" />

is that a right way of doing it...?

To run SQL script while installing your DB, have a look at this article:

http://msdn2.microsoft.com/en-us/library/49b92ztk(VS.80).aspx

Regards,
Mehdi

|||

Yes Mehdi you are right, Using the <appSettings> section to add these keys is the right approch.

<appSettings>

<addkey="ReportServerUrl"value="http://ServerName/ReportServer"/>

<addkey="ReportPath"value="/ReportPath/"/>

</appSettings>

and acces it like this -

ReportServerUrl =newUri(ConfigurationManager.AppSettings["ReportServerUrl"]);

thanks for the link.

|||

Deploying a SQL Server Reporting Services 2005 report via an MSI -

http://blogs.msdn.com/bimusings/archive/2006/03/01/541599.aspx

package with a custom component (log provider) runs in BIDS, but doesn't run where deployed.

Hi,

I have a package with a custom log provider, which runs in BIDS. However when I deploy the package onto SQL Server and run it on the deployed machine, if fails:

"failed to decrypt protected XML node DTS:Password...key not valid for use in specified state..."

Now this is definately to do with the custom log, as if I take it out & redeploy, I can run it on the deplyed server + also run it within a job. I have entered the custom log provider library (+ other required DLLs) in the GAC on the deployed machine, but I'm clearly missing something.

Any ideas pls? I'm really stuck.

Many thanks in advance,

Tamim.

Please search this forum...

You'll need to set the package protection level to "DontSaveSensitive" and then issue a password via the /SET command line switch. OR, you could try to promote to the server using SQL Server users/role level security, maybe....|||

Thanks for the quick reply Phil, but that didn't work.

Is there anything extra I need to do/bear in mind with respect to the signed assemblies that comprise the custom log provider?

|||

Tamim Sadikali wrote:

Thanks for the quick reply Phil, but that didn't work.

Is there anything extra I need to do/bear in mind with respect to the signed assemblies that comprise the custom log provider?

What do you mean that didn't work? That error message states that you are using indicates to me that the protection level is set to EncryptSensitiveWithUserKey, which means that ONLY the user who built the package can execute it.

Package deployment question

Hi,

I have a standalone SSIS package that I wish to deploy from the file system, as opposed to SQL Server deployment.

Anyways, I was wondering if there's a command line utility for running SSIS packages on an ad hoc basis? What I was thinking was that I would put the call to the command line in a stored procedure using xp_cmdshell, so that the package can be called that way.

Am I thinking about this correctly?

Thanks

dtexec.exe is what you'll need to run from the command line.|||

Thanks. Is this what people generally use for file system deployment?

Question:

Do I need to do anything special with the package first? Or just call it with dtexec.exe from wherever the pkg resides on the file system?

|||

I guess I am wondering about protection levels... and how to work with protection levels when using file system deployment?

I find this all very confusing!

Are there any good articles that discuss all the in's and out's of proper pkg deployment?

|||

Yes, dtexec is what is used to execute from the file system (and from the database). DTEXEC is the runtime engine for SSIS.

If you are running from the file system, I'd recommend setting the ProtectionLevel to SaveSensitiveWithPassword, and use the Decrypt switch to pass it to the package.

|||

Ok thanks.

Q. Concerning "sensitive" data, I'm not 100% sure what constitutes sensitive data? FTP conn mgr passwords and OLE DB conn mgrs using SQL Server logins where you must specify the passwords?

Q. Another question: What if the OLE DB conn mgrs use Windows Authentication to connect to the db and someone else logs on to the server to run the package (and they don't have write permissions to the database)? Will this cause the conn mgr not to be able to connect t the db? In which case, even having a password won't help, right?

Q. But it seems that file system deployment forces the issue, so that I MUST set up a password if I am not deploying to SQL Server, correct?

Thanks much

|||

Hi,

I set the encryption to "EncryptSensitiveWithPassword", and attempted to run this from Management Studio:

EXEC master..xp_cmdshell 'DTExec /FILE "D:\myPackage.dtsx" /DECRYPT "password"'

It appears to run, but these are some of the errors I get. It looks like both it doesn't like the key, and also that SSIS isn't properly installed on this server. Two separate issues, but how can I resolve the key problem first?

Thanks

output

NULL

(1 row(s) affected)

output

Microsoft (R) SQL Server Execute Package Utility

Version 9.00.1399.06 for 32-bit

Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

NULL

Started: 7:56:53 PM

Error: 2007-06-18 19:56:53.89

Code: 0xC0016016

Source:

Description: Failed to decrypt protected XML node "DTSassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that

the correct key is available.

End Error

Error: 2007-06-18 19:56:54.56

Code: 0xC0202009

Source: myPackage Connection manager "myServer.myDB.mySQLServerLogin"

Description: An OLE DB error has occurred. Error code: 0x80040E4D.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'mySQLServerLogin'.".

End Error

Error: 2007-06-18 19:56:54.58

Code: 0xC0012024

Source: Script Task

Description: The task "Script Task" cannot run on this edition of Integration Services. It requires a higher level edition.

End Error

Warning: 2007-06-18 19:56:54.58

Code: 0x80019002

Source: OnError

Description: The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCoun

t or fix the errors.

End Warning

Error: 2007-06-18 19:56:54.58

Code: 0xC00291EC

|||It seems as though the passwords do not match. CaSE matters. And ensure they match. You did specify a password in the package properties box, right?|||You also have a permission issue to connect to the SQL Server DB. Make sure that whatever user is running the package has rights to connect to the SQL Server|||

Yes, the password is set on the package. It's just "password", so it's no brainer.

So, Rafael, you're saying that the /Decrypt switch is not related to the rights to connect to the server?

I thought that having a password on the package would take care of all the access issues. That is, all the connections are already set in the package, so whoever runs it doesn't need to worry about the underlying connections?

So, how do I resolve this?

Basically, I am using a SQL Server user with read/write permissions in the database for all my OLE DB conn mgrs. Why does this connection fail when I use DTExec? What piece am I missing here?

Need help! Thanks.

|||Have you just tried running this on your own with dtexec on the command line? Without using xp_cmdshell?|||

I think these are 2 separate issues. The package password will give the rights to read and execute whatever is inside of the package. I made my coment based on other error I see in the log:

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'mySQLServerLogin'.".

It looks like the login credential to connect to that SQL Server are not right.

|||

Rafael Salas wrote:

I think these are 2 separate issues. The package password will give the rights to read and execute whatever is inside of the package. I made my coment based on other error I see in the log:

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'mySQLServerLogin'.".

It looks like the login credential to connect to that SQL Server are not right.

But that would be because the sensitive information couldn't be decrypted, no?|||Sadie,
Try using EncryptAllWithPassword instead.|||

The package is set up with OLE DB conn mgrs that use SQL Server users.

When I run DTExec.exe, I am doing so as my Windows Login, which has admin rights on the server. I've never had permissions problems with the database.