MSBI (SSIS/SSRS/SSAS) Online Training

Wednesday, May 11, 2011

How to generate SSRS Report and How to deploy in MOSS


Hi,
Here you can learn how to deploy/integrate a SSRS report with MOSS application.

Assumptions:
Report Server has been configured for SharePoint Integration Mode.
SharePoint Report Server Integration Feature is activated.
Appropriate Content Types (Report Builder, Report Builder Report, Report Data Source) have been added to a Document Library within a site.

For further information on these configuration topics, see Deploying SSRS with SharePoint Integration.

Assume the following server/site/library/folder names:
• Server Name = MyServer
• SP Site = SiteABCReports
• Doc Library = SSRS Reports
• Data Sources Folder (optional) = Data Sources
• Report Models Folder (optional) = Models
• Reports Folder (optional) = Reports

Deploy Report Designer Reports

From within a SQL Server Business Intelligence Studio (BIDS) Report Server Project, go to the project’s Property Page (right-click project in Solution Explorer and select Properties).
Apply the following deployment settings:
• Overwrite ExistingDataSources = True
• TargetDataSourceFolder = http://MyServer/SSRS%20Reports/[Data%20Sources]
• TargetReportFolder = http://MyServer/SSRS%20Reports/[Reports]
• TargetServerURL =http://MyServer

Where [‘xxx’] denotes optional.

Things to note:

• A TargetReportFolder must be specified. The folder can be the document library or a folder within the document library.
• The TargetDataSourceFolder is optional. If one is not specified, the Data Source will be deployed to the TargetReportFolder.
• For both target properties, if the folder within the document library does not already exist, it will be created upon deployment.
• Relative paths are not valid.
• The replacement of the space character with %20


See the below article for step-by-step approach to deploy the report on MOSS.
http://mosshowto.blogspot.com/2009/02/reporting-services-2008-report.html

Here you can download some of the sample SSRS reports,it’s really nice work.
http://blogs.msdn.com/b/chrisfie/archive/2008/04/10/new-sql-reporting-services-sample-reports-for-project-server.aspx

Linked server in sql server 2008


Hi,
When you are setting up a linked server, register the connection information and data source information with SQL Server. After registered, that data source can be referred to with a single logical name.
You can use stored procedures and catalog views to manage linked server definitions:
• Create a linked server definition by running sp_addlinkedserver.
• View information about the linked servers defined in a specific instance of SQL Server by running a query against the sys.servers system catalog views.
• Delete a linked server definition by running sp_dropserver. You can also use this stored procedure to remove a remote server.
You can also define linked servers by using SQL Server Management Studio. In the Object Explorer, right-click Server Objects, select New, and select Linked Server. You can delete a linked server definition by right-clicking the linked server name and selecting Delete.
When you execute a distributed query against a linked server, include a fully qualified, four-part table name for each data source to query. This four-part name should be in the form linked_server_name.catalog.schema.object_name. For more information, see Distributed Queries.

Step-by-step procedure available in below article.
!! Thanks madhu !!
http://madhuottapalam.blogspot.com/2008/08/configuring-linked-server-from-sql.html

Linked server in sql server 2008


Hi,
When you are setting up a linked server, register the connection information and data source information with SQL Server. After registered, that data source can be referred to with a single logical name.
You can use stored procedures and catalog views to manage linked server definitions:
• Create a linked server definition by running sp_addlinkedserver.
• View information about the linked servers defined in a specific instance of SQL Server by running a query against the sys.servers system catalog views.
• Delete a linked server definition by running sp_dropserver. You can also use this stored procedure to remove a remote server.
You can also define linked servers by using SQL Server Management Studio. In the Object Explorer, right-click Server Objects, select New, and select Linked Server. You can delete a linked server definition by right-clicking the linked server name and selecting Delete.
When you execute a distributed query against a linked server, include a fully qualified, four-part table name for each data source to query. This four-part name should be in the form linked_server_name.catalog.schema.object_name. For more information, see Distributed Queries.

Step-by-step procedure available in below article.
!! Thanks madhu !!
http://madhuottapalam.blogspot.com/2008/08/configuring-linked-server-from-sql.html

SSIS Migration from 2000 (dts) to 2008 (dtsx)


Integration Services provides the DTS Package Migration Wizard for migrating SQL Server 2000 Data Transformation Services (DTS) packages that were created by using the DTS tools and object model.
Not all packages can be migrated completely. For example, some DTS tasks do not map to Integration Services tasks, custom tasks cannot be migrated, and some scripts in ActiveX Script tasks or in data transformations cannot be migrated. For more information, see Migrating Tasks and Known DTS Package Migration Issues.
Packages left in the DTS package format can be used in Integration Services solutions and can also continue to run as stand-alone DTS packages.
Many DTS packages are password-protected. If you migrate these packages, the Package Migration Wizard prompts you for their passwords. You cannot migrate a password-protected package unless you provide the correct password.
SQL Server does not install support for Meta Data Services. The Package Migration Wizard supports Meta Data Services (Repository) packages only if SQL Server 2000, the SQL Server 2000 tools, or the Repository redistributable files are installed on the local computer. When the Repository files are present, the Package Migration Wizard can migrate DTS packages that were saved to Meta Data Services. When the Repository files are not present, the Package Migration Wizard can only migrate DTS packages that were saved to SQL Server or to structured storage files.
You can find some known issues in migration here

http://www.simple-talk.com/sql/ssis/dts-to-ssis-migration/
http://technet.microsoft.com/en-us/library/ms143462.aspx

I have some nice links which describes step by step how to migrate DTS to SSIS 2008.
I hope that will help u goto following urls:

http://www.infosys.com/microsoft/resource-center/DTS-SSIS-2008-migration.pdf

http://weblogs.sqlteam.com/derekc/archive/2008/02/19/60518.aspx

http://wiki.sqlis.com/default.aspx/SQLISWiki/MigrationWizardSteps.html


Downloads:
http://pragmaticworks.com/DTSxChange-vs-MSWizard.pdf

Video link for "how to migrate SQL Server 2000 DTS packages to SQL Server 2008"

http://www.msdev.com/Directory/Description.aspx?eventId=493