Transcript Execute DTS 2000 packages from SSIS 2005

Upgrading to SQL Server 2005
Keith Burns
Data Architect
DPE, Microsoft UK
Agenda
How to upgrade
Reporting Services
Analysis Services
Data Transformation Services
Upgrading
SQL Server Reporting Services
Why Upgrade SSRS?
 UI Enhancements






Rich Client Printing
Enhanced Expression Editor
Multi-Value Parameters
Date Picker
Interactive Sort
Floating Headers
 Enhanced Analysis Services Support
 Graphical MDX designer
 MDX Parameters
 Server Aggregate Support
 Tool Integration (Mgmt Studio/BI Dev Studio)
 Report Builder
 ReportViewer Controls
Reporting Services 2005 Setup
 Part of the standard installation
 SQL Server 2005 RS setup in has two modes:
 Default Configuration
 Files Only Installation
 Default configuration assumes
 Default web site with newly created App Pool
 Relational database engine installed at same time
 Use service account for database connection
 Configurations no longer included in setup
 Remote RSDB (including SQL 2000)
 Scale-out (web farm) installation
 SMTP Server configuration
 After 2005 setup, E-mail delivery is DISABLED
Demo
Reporting Services 2005
Configuration Tool
Supported Deployments
Single Server
 Report Server and SQL
Relational engine on same
computer
 Same or different instances
Server
Report
Server
Two Server
Server

Report
Server
SQL
Relational
Engine


Report Server on
server 1
SQL relational
engine on server 2
Addresses
resource
contention
SQL
Relational
Engine
RSDB
RSDB
Scale-out
Server
several servers
 Report Servers
share same RSDB
 SQL relational
engine on another
computer*
SW/HW NLB
 Report Server on
Server
Multi-instance (2005
Only)
 Several Report Servers on
Report
Server
Server
SQL
Relationa
l Engine
Report
Server
RSDB
Server
a single server
 Each has own RSDB
 May use same SQL
relational engine, can be
remote
 RS 2000 one instance
allowed, always Default
Server
Report
Server
(Default
Instance)
Report
Server
(Named
Instance)
SQL
Relational
Engine
RSDB 1
RSDB 2
Upgrade Process - UI
Install prerequisites
.Net Framework 2.0
Select Features
New
Instance
Existing
Instance
Select Instance
Select which features
to upgrade
Yes
Install the default
Configuration
No
Specify credentials used
during upgrade
Run Upgrade blocker
checks
Proceed with upgrade
Most likely
scenario for
new install
Probably a
“migration” upgrade.
Will require running
RS Configuration
afterwards
Upgrade Impacts
 Existing reports continue to work
 Existing applications continue to work
 Existing data sources can be used for
reports
 RS 2005 can use SQL Server 2000 data sources (AS & SQL)
 3rd party data sources do not need to change
 WMI provider is changed
 New namespace, much richer
 Old namespace is not supported in RS 2005
 Changed location on disk
C:\Program Files\Microsoft SQL Server\MSSQL.X\Reporting Services\ReportServer
Upgrade Impacts: Existing Reports
 RS 2000 RDL
 Can be published to RS 2005
 RDL is not changed when setting/getting from server
 Published Reports
 Snapshot format is upgraded on the fly at first access
 RS 2000 Report Designer continues to work
 Requires Visual Studio .Net 2003
 Does not work on Visual Studio 2005
 VS .Net 2003 and VS 2005 run side-by-side
 RS 2005 Report Designer
 Requires Visual Studio 2005 IDE – included in SQL Server 2005 box
 Upgrades RS 2000 RDLs on first load
 Does not output RS 2000 RDLs
Migrating Report Server
 Migrate to keep your production system
running
 Allows for a test deployment/roll out process
 When to migrate:




You encounter an upgrade blocker
Large scale deployment
Uptime-requirement eg internet facing
Changing deployment
 New hardware
 Topology change (esp. if cannot upgrade Relational
Engine)
Upgrade Blockers – RS
 Customized Virtual Directories
 Usually changed for Internet scenarios
 Custom virtual directory name DOES NOT block upgrade
 Custom extensions
 Complexity of handling possible extension scenarios is high

One assembly, multiple assembly, folder structure assumptions
compiled into extension, GAC, etc.
 Did not have time to handle all possible cases
 ASP.Net account stored encrypted in registry
 ProcessModel tag in Machine.config
 Workaround is to unencrypt account for upgrade, then change
back
 Bug with detection logic
 Must have Report Manager installed to upgrade to 2005
Upgrading Relational Engine
 NOT REQUIRED to upgrade Reporting Services
 Relational Engine upgrade changes: Files on disk
 Database format
 Does not affect RSDB contents nor schema
 If Report Server and SQL Relational Engine are in
different instances:
 You can upgrade Report Server without upgrading RE
 You can upgrade RE without upgrading Report Server
 If Report Server and SQL Relational Engine are in the
same instance (Default):
 Either upgrade RS and RE at same time
 Or migrate RS to another computer/instance
How to Migrate Reporting Services
Covered on the Upgrade DVD
4.
1. Backup
a. Backup symmetric key
b. Backup RSDB and RSTempDB
c. Copy configuration files
i.
ii.
iii.
iv.
v.
vi.
Rsreportserver.config
Rswebapplication.config
Rssrvpolicy.config
Rsmgrpolicy.config
Rsreportserverservice.exe.config
Web.config for both RS and RM
Migrate configuration settings
a.
i.
ii.
iii.
iv.
b.
c.
5.
2. Install SQL Server 2005
b.
c.
3. Run configuration tool
a.
Configure Virtual Directories to match previous
configuration
b. Set service accounts
c. Upgrade the RSDB – use a COPY/Backup
i.
RSTempDB automatically upgraded
6.
7.
d. Configure report server to use the upgraded
RSDB
Configure ViewState encryption if in a scale-out
Need to copy Delivery, Rendering, Data
extensions
Need to update and recompile Security
extension
Update code access security settings
1.
2.
Create New Virtual Directories
i.
Web.config for RS and RM
ReportingServicesService.exe.config
Update and copy extensions
a.
Choose ‘Install but do not configure’ option
for RS
Rsreportserver.config
Rswebapplicaiton.config
Rssrvpolicy.config
Rsmgrpolicy.config
Need to copy tracing settings (if customized)
i.
ii.
d. Capture Virtual Directory settings
a.
Can just copy over the report server config files
8.
Rssrvpolicy.config
Rsmgrpoclicy.config
Restart the report server Windows
service
Test report server and report
manager virtual directories
Test your reports
Quick Discussion
 Does this sound complex?
 How many people are using RS today?
 How many people do NOT have all RS
components running on a single box?
 How many people are not running a
default RS install?
 How many people have ALL their reports
backed up in Visual Studio Projects?
Upgrading
SQL Server Analysis Services
Upgrading to SSAS 2005
 Three options for moving to AS2005
 Upgrade (in place)
 Migration (side by side)
 Redesign
 For each option we will cover:
 Techniques
 Pros
 Cons
 Upgrade Advisor
 Known Backward Compatibility Issues
 Upgrading Client Applications
Upgrade Advisor
Known Upgrade Issues
 Run Upgrade Advisor before any Migration or
Upgrade!
 Discontinued:
 Virtual cubes, virtual dimension, custom level formulas
 Mostly replaced by other features
 Linked measure groups, multiple hierarchies, MDX
scripts
 Deprecated:
 ie may be discontinued in next release (after SQL2005)
 Calculated cells, cell evaluation list…
 Behavioral changes in some features
 Check Books Online for all upgrade issues and
mitigations
Upgrade
 Run Upgrade Advisor and evaluate results
 Make modifications to AS2000 database if
necessary
 Backup all databases and registry settings
 Run Setup and choose to upgrade default
instance
 Process the database from Mgmt Studio
 Upgrade only brings over metadata to AS2005
 Upgrade reuses migration logic under the
covers
Upgrade
Pros and Cons
 Pros
 No new hardware required
 Streamlined process that happens during setup
 Cons
 Data is unavailable until cubes are
reprocessed
 Side by side comparison with AS2000 not
possible
 Tougher to troubleshoot upgrade issues
 Doesn’t make best use of AS2005 features
Migration
 Run Upgrade Advisor and evaluate results
 Make modifications to AS2000 database if
necessary
 Backup all databases and registry settings
 Install AS2005
 If on same machine as AS2000  must be named
instance
 On different machine  preferable if AS2000 is live
 Run Migration Wizard (from SQL Management
Studio)
 Process the database from Mgmt Studio
 Migration will only bring over metadata to AS2005
Demo
Analysis Services 2005
Migration Wizard
Migration
Pros and Cons
 Pros
 Migration Wizard is fast and easy - very little additional
overhead from upgrade
 Supports migrating individual databases
 Enables customer to move databases one by one through
migrate  process  verify  production
 Side by side comparison with AS2000 possible
 Easier to troubleshoot upgrade issues
 AS2000 data is available to user during migration
procedure
 Cons
 Doesn’t make best use of AS2005 features
Upgrade/Migration logic
 Upgrade/migration logic does best possible job
while preserving compatibility with AS2000
database
 Query results, schema rowsets, unique names, etc.
 Some objects/features are not migrated:
 Drill through
 Linked cubes and dependant objects
 Remote partitions
 However migrated database is not best practice
for AS2005
 Different from database redesigned using AS2005
tools
Post Upgrade Modifications
 Two options:
 Connect to live database via BI Dev Studio
and edit
 Import live database into BI project, work
offline and deploy to server
 Use wizards and designers to add
features:
 KPIs
 Perspectives
 Translations
 MDX Script
 Drill through and report actions
Redesign Database
 Alternative to Upgrade and Migration
 Use BI Dev Studio wizards and designers
 DSV wizard & designer - model relational
schemas, views, calculated columns
 Dimension wizard & designer
 Cube wizard & designers
 BI wizard for adding calcs – time intelligence,
account intelligence, etc.
 Mining model wizard & designer
Redesign Database
Pros and Cons
 Pros
 Produces best practice design





Multiple hierarchies & attributes
Multiple measure groups & perspectives
Richer relationships – many to many, referenced
MDX Script – central place for all calcs
Single mining structure with multiple models
 Produces more maintainable design than
migration logic
 Opportunity to revisit DW design, enhance the
application
 Cons
 Higher upfront cost, amortized over time
Upgrading Client Apps
 End user apps
 eg Excel, Proclarity, Panorams, Custom apps ..
 How To Upgrade

Install MSXML6 and AS2005 OLEDB provider from SQL2005 Feature Pack
(www.microsoft.com/downloads)
 Compatible with PTS8 – most end user apps should “just work” after
upgrading the server
 No upgrade/install required for ADOMD or ADOMD.NET
 Optionally install new version of ADOMD.NET from SQL2005 Feature Pack
for enhanced object model (for new apps)
 Admin applications
 Eg Analysis Manager, DTS2000 packages, custom DSO app (e.g.
parallel processing tool)
 Use DSO (Decision Support Objects) to communicate with AS2000
server
 How to upgrade

Install Management Objects Collection (contains AMO) from SQL2005
Feature Pack
 Install Backward Compatibility Components (contains DSO9) from SQL2005
Feature Pack
Upgrading
Data Transformation Services
Why Upgrade to SSIS
 DTS - SQL Server 7.0
 “Visual BCP” – a useful utility
 DTS - SQL Server 2000
 Easy (but slow) workflow & transform engine
 Customizable
 SSIS – SQL Server 2005
 A completely new codebase
 Enterprise class ETL
 Exceptional BI integration – and more
 Rich APIs and extensibility
Local Server Upgrade
 Tools install includes DTS 2000 runtime.
 DTS packages on server, file system are
untouched by upgrade.
 DTS packages on upgraded SQL 2005
server remain available for execution and
maintenance.
 DTS agent jobs run without interruption.
 Apps written to DTS API continue to work.
 Server upgrade removes SQL Server 2000
Admin Tools.
 If you need legacy tools, install 2000 tools alongside
2005 tools.
 Repository support withdrawn.
Maintaining DTS 2000
Packages After an Upgrade
 Use SQL Server 2005
Management Studio to
manage DTS packages
on both 2000 and 2005
servers.
 Access via Legacy node
in Object Explorer
 Legacy UI components
don’t ship with 2005
 Download DTS 2000
Designer Components
http://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&DisplayLang=en
Demo
Maintain DTS 2000
packages with 2005 Tools
Support for Migration
 Execute DTS 2000 Package Task
 Migration Wizard
 Upgrade Advisor
Execute DTS 2000 Package Task
 Use the Execute DTS 2000 Package task to
invoke DTS packages from within an SSIS
2005 package.
 Wrap legacy packages to take advantage of
improved SSIS:
 Looping and sequencing constructs
 Package security
 Event handling, restartabililty
 SSIS Service package enumeration and
management
 Embed legacy package in 2005 package for
easier deployment.
 Note – 32-bit only
Demo
Execute DTS 2000 packages
from SSIS 2005
Package Migration Wizard
 “Best Effort” migration
 Creates new SSIS package; leaves original in
place.
 Migrate what we can
 Variables and control flow
 Most tasks
 Wrap what we can not
 Complex Data Transformation Tasks
 AS Tasks
 Some constructs not supported
 Transactions
 Dynamic Property Task
 Access to old API; script on step
Package Migration Cases
 Workflow
 Simple Data Transformation
 Complex Data Transformation
 Self-modifying packages
 Dynamic Properties
 Nested packages
 Custom Task
Complexity
Workflow Packages
 Most tasks migrate seamlessly.









FTP
Execute Process
Execute SQL
Bulk Insert
Copy Objects
Send Mail
Execute Package
MSMQ
Transfer Tasks
 Control Flow and Precedents migrate without issue.
 Global Variables too.
 Active X Script task may require fix-up if referencing old API.
Simple Data Transformation
 DTS Data Transformation and SSIS Data
Flow object models don’t map 1-1.
 Goal: Migrate all packages created by the
Import/Export Wizard and any others with
equivalent functionality.
Complex Data Transformation
 Where migration not supported, migration
wraps original task in an Execute DTS
2000 Package Task.
 Script or other transforms
 Multiphase pump, lookup, etc.
 DDQ, or parallel data pump.
 Redesign with SSIS 2005 transformations
for performance
 Add error handling for reliability
Self-Modifying Packages
 DTS had limited control flow constructs.
 Basic looping required altering step status
values in a running package.
 SSIS limits scope of changes to current
task, but provides superior control flow
constructs.
 After migration, script that accessed
legacy API will fail.
 Update migrated packages to replace such
script with new control flow.
Dynamic Properties Task
 The purpose of the Dynamic Properties
Task is to navigate the legacy API and
update properties of the running package.
 Migration wizard replaces the Dynamic
Properties task with a placeholder.
 In SSIS, use configurations and property
expressions to do the work done
previously with the Dynamic Properties
Task.
 Update migrated packages to use these
features.
Nested Packages
 Migration replaces 2000 Execute Package
Task with a 2005 Execute 2000 Package
Task.
 New parent package continues to point at
the originally invoked 2000 child package.
 Every time a child package is migrated, a
change to parent package is required to
bring the new 2005 package into the
control flow.
 Also applies where child package is
invoked from script.
Custom Tasks
 Custom tasks are wrapped in the migrated
package. They will continue to run.
 Consider porting custom task to any .Net
language.
 No more threading issues.
 Easier API, development, maintenance.
 Take immediate advantage of property
expressions, logging, events, etc.
 Replace old custom tasks with new ones
in migrated packages.
Demo
Using the Upgrade Wizard to
upgrade DTS packages to
SSIS
DTS Recommendations
 DTS API is deprecated in this release.
 Complete migration to SSIS before next
release of SQL Server.
 Limit further investment in DTS 2000
packages.
 Migrate quickly where complexity, stability,
performance, of packages are low.
 Rewrite where complexity, performance,
features justify.
 E.g. order of magnitude differences in script,
lookup performance.
© 2005 Microsoft Corporation. All rights reserved.
This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.