Chapter 5: Debugging & Error Handling with SSIS Packages.

Create control flow by using the control flow designer.

• Can co-ordinate txns with package restart - can be configured to restart from pt of failure when pckg rerun.

Configure txn handling for packages, containers, tasks.

• DTC - start svc + tasks that are to be part of txn must work natively with DTC svc.
• TransactionOption - Required.
• Txns work at control fow lvl, NOT data flow.

Connecting Control Flow Objs with Precedence

• Precedence constraints - control flow connectors bet. tasks & containers. NOT in data flow. Connectors there are 'data paths'.
• solid lines - && precedence -
• dashed lines - precedence - task will run when either of the precedence conditions is met.
• functional symbols - an SSIS expression has been embedded in the constraint for evaluation.

Set checkpoints to define restart pts

• restart @ point of failure.
• Enable pckg to use chkpts - PackageProperty.SaveCheckpoints -> TRUE, PackageProperty.CheckpointFileName - supply, PackageProperty.CheckpointUsage - IfExists - pckg runs fom beginning if file not present, or run from identified pt where exists. (If CheckpointUsage = Always, file must be present or pckg will not start).
• Set checkpoints at various stages of the pckg -
• 
  

Create Event Handlers

• -> Event Handlers tab in pckg designer, select executable, select event handler event.
• event handlers assigned to a particular scope will propogate downwards to child events when the event fires.
• Capturing err. info. with OnError event - capture sys. variables e.g. ErrorCode, ErrorDescription, SourceName (i.e. the task).
• Event handlers can be turned off for any task/container - DisbaleEventHandlers -> TRUE.

Implement error handling

Handle errors by configuring data flow paths

• Data paths - output rows that have successfully gone thro a component, as well as errored rows where error output is set to ignore failure for said task.
• Using error paths to route failed rows - red connectors. Contain data rows that fail in a component, when the error rows are set to be redirected. To use, configure the task's error output.
• not all components use error paths e.g. Multicast.
• error output - 3 options: Ignore Failure, Fail Transform, Redirect Row.
• error handling options are available for entire row + per col.

Handle errors by configuring control flow paths

Debug Packages

Debug progress reporting

• Even after an error, other components in the pckg might still be executing.
• Allowing mult. errs - MaximumErrorCount property -

View intermediate results by using debug windows

Examine the package state by setting breakpoints

• Control flow debugging - set bpts, which will pause the control flow execution so that you can observe the execution state.
• bkpts - only in control flow.
• 
  
  
  

Chapter 4: Developing SSIS Packages.

Create a Package -

Use BIDs -

• Needed because 'more complicated' packages cannot be created thro I/E Wizard.
• Need to create a set of co-ordinated pckgs.

Use SQL's Import/Export Wizard -
I know this already!!

Create data flow by using the data flow designer -

Configure sources & destinations -

• Navigate to data flow tab.
• 3 types of obj in toolbox:
  1. Data flow source adapters - along with destinations, they use package connections. Only raw file adapter doesn't use PC.
• 2. Data flow transforms - a transform performs an op. on one row at a time or several rows at a time. Types of tranforms - a) Logical row-lvl transform - performs op on rows without needing other rows. b) Multi-input or multi-output transforms - either can work with > 1 input or generate >1 output. Used to combine or branch data. Merge & Merge Join require sorted inputs. These 2 transforms will wait for rows from either input based on the defined sort order, to preserve the sorted output. Thus rows may not immediately be sent out on the output path. c) multi-row transforms - perform work based on criteria from multiple input rows, or generate multiple output rows from a single row. Such transforms can be more intensive in operation and memory overhead. Sort, Aggregate - these are blocking transdforms - all output rows are blocked, allowing the tranform to perform the work before sending rows downstream. d) Advanced data-prep transforms - e.g. script component, fuzzy lookup.
• 3. Data flow destination adapters - Set ValidateExternalMetadata property -> false if e.g. a destination tbl is not available at design time, as it is created during execution. OLE DB Destination Adapter - can be configured for bulk insert, otherwise data is inserted 1 row at a time.
• connect srcs, tranforms & destinations by connecting data paths (green or red).

Configure transformations -

Create control flow by using the control flow designer -

• The control flow is the workflow engine & contains control flow tasks, containers & precedence constraints. In BIDs we define control flow objs with the control flow designer (1st tab).
• (Dataflow is directly related to processing & transforming data from sources -> destinations).
• 3 primary types of control flow objs:
• 1. Control flow tasks - workflow objs that perform operation-lvl jobs.
• 2. Containers - provide a grouping mechanism for tasks.
• 3. Constraints - Allow one to connect tasks & containers and thus define exec order + precedence.

Control Flow Tasks -

• Each CFT has an editor to configure it.
• Pckg must contain at least 1 task.

Control Flow Containers -

• A container allows one to grp tasks to control parellelization, ordering, logging & txns.
• Containers also provide the ability to execute the tasks within them several times based on iterative requirements.
• Sequence container - Lets you organise subordinate tasks by grping them together, & then apply txn or logging to the cont.
• For Loop container - As Seq. Cnt. but also allow for running of tasks therein to be run multiple times, based on an eval. condition e.g. looping from 1 to 10.
• ForEach Loop Cont. - instead of providing a condition expr. for looping, you loop over a set of objs such as files in a folder. Can iterate over diff. types of objects.

Sequence tasks by using precedence constraints -

Organize tasks by using containers -

Manage SSIS package configurations -

Connect to different data source types by adding connection managers -

• Data Sources - An SSIS Project object. live at project lvl and are found in Solution Explorer under logical folder 'Data Sources'. Once an existing data src is changed you need to open each package to synch the conn string inside each pckg, with the data src.
• Connections - scoped @ pckg lvl. Connections can be based on project data sources (New Connection from Data Source...) or can stand alone within pckgs.

Chpater 3: Migrating to SQL Server 2005 BI Components

Definitions
Measures - the numbers that are analysed. E.g. sales amts, inventory counts, account balance amts.
Dimensions - qualify whats being analysed. E.g. if analysing sales amts, then the following dimensions will relate to each sale: pdt, customer, sale date & store.
Attributes - Dimensions are made up of attributes. They provide detailed descriptors about the dimension. e.g pdt dimension will have attributes: Category, Model, Size & Pdt Name. You can summarize/analyse data at an attr lvl.
Hierarchies - attr's combine together to make hierarchies. (Yr -> Qrtr -> Mnth -> Day).
Cubes - contain dimensions, attributes, measures.

The 2 features with changes that have the largest impact on migrating are attributes and cubes.


Managing SSAS

• In-place migration - when install 2005 on same server on which def. instance of 2000 already installed. The 2005 installation process wil auto invoke the in-place upgraqde, thus existing cubes will be auto migrated.
• side-by-side migration - install 2005 on a new server OR same server as named instance. (TIP: install a named 2005 instance alongside an unnamed 2000 instance. Then after successful auto migration of cubes uninstall 2000 and rename 2005 to default instance).

Migrate from SSAS 2000 to SSAS 2005 ~ Migration Wizard

• Attribute Changes - in 2000 a dim. cld only contain 1 hierarchy. In 2005 a dim. can have >=0 hierarchies.
• Cube changes - In 2000 only 1 'fact' tbl. In 2005 >=1 fact tbl. In 2000 whilst cubes can be combined into virtual cubes, there are limitations on how the fact tbls can relate to one another.

1. Run on server hosting 2005. Need lcl or network access to 2000 cube.
2. IDs incompatibilities.

Post Migration considerations -

Migrate to SSIS packages

• DTS & SSIS - diffs -
• DTS is workflow engine with a few data transform fntns. SSIS - enterprise-lvl ETL tool with richer workflow engine, better tranform engine, + ability to perform data operations.
• Workflow-drive Vs Dataflow driven.
• synchronous data processing Vs asynch data-processing.
• one-to-one data copies Vs many-to-many data processing.
• Scripting for advanced operations Vs minimal scripting due to broad capabilities
• all or nothing Vs package restartability + txns support

• Executing & Managing DTS in SQL 2005 -
• Installation Runtime - when installing 2005, the Advanced Options allow one to choose to include the DTS Runtime Engine as part of the 2005 installation.
• SSMS - Can manage + design + edit (install DTS Designer components) DTS packages in 2005 Mgmt Studio. Store DTS pckgs in msdb - Mgmt\Legacy\DTS.
• Execute Package Task - exec DTS pckg from within SSIS pckg.

• Running Package Migration Wizard -
• can select mult. packs to migrate.
• Wizard logs progress to a flat file.
• If invoked wizard from within BIDS, the output SSIS pckgs will be auto added to the existing project.

• Post-Migration Steps -
• Some DTS func. does not migrate well:
• Dynamic Property tasks in DTS do not get migrated. Instead the details of the dynamic property are entered into a Script task as comment lines.
• Analysis Services 2000 Processing Task not supported in SSIS. Will need to call an .exe or use Script Task.
• UDL (Universaal Data Link) files not supported in SSIS - so if DTS pckg contains a conn. pointing at UDL file conn. in SSIS will need changing.
• Data Pumps that involve scripting or certain conns (e.g. Excel) will get embedded in an Execute DTS 2000 Package Task.
• Taking Advantage of SSIS features -
• Convert any Exec DTS 2000 Tasks -> Data Flow Task.
• Change Exec SQL Tasks -> Data Flow Tasks - DTS relies heavily on relational db engine to perform transforms, and thus you can reduce need for staging tbls.
• Add error handling.
• Add checkpoints - i.e. built-in restart features, which allow you to start a package fro ma prev. point of failure.

Create a package

Use the Package Migration Wizard

Chapter 2: SSRS, SSAS configurations & settings

SSAS, SSRS - require the setup of server-side config properties + instance-wide settings.

Configure SSAS

Configure Query/Error Logging

• Log\FlightRecorder - an error and activity log for SSAS. Set Enabled -> TRUE. Rsetart for chnage to take effect. By def., it does not capture queries. TraceDefinitionFile property - define your own trace, capture the defn, and override the default capture.
• Log\QueryLog - enable capture of queries that are run against any SSAS db in the instance. You can use captured queries to optmiize aggregation.

Configure Disk Allocation

Configure server and db roles and permissions

> install of SSAS instance, the SSAS svc needs configuring with SSAS security roles for administration.
Setting SSAS Admin Roles and Permissions:

• There are 2 primary security roles:
• 1. Server Role - provides access to complete SSAS server fntns, incl. data & processing. Provides complete SSAS access to assigned users. Manage users/grps assigned to role ~ Mgmt Studio. Members of the lcl admin grp on the same server as the SSAS instance are automatically added to Server role, although this grp does not appear on the server role list.
• 2. Database roles - define db-lvl admin/mgmt tasks and end-user data access. General pg - name + describe the role + assign db permissions: Full Control (Administrator), Process db (user is limited to processing the db in which the role is created), Read definition (lets a role member see the full definition of a db - no data-access or processing rights). ~ Membership tab, can manage lcl/domain users/grps w.r.t the role.
• SSAS uses Windows Auth only - no SQL Auth option => SSAS security can olny be assigned to domain/local users+grps.

Configure an SSRS infrastructure

If SSRS is installed with def. config -> no further config req.
If installed 2nd (named) instance OR need to configure for Internet deployment -> special config req.

Configure SSRS for Internet deployment
1. Create virtual directories in IIS - The Web Svc that SSRS uses for report mgmt, publishing + rendering is located in Report Server Virtual Directory. (Def. name - Report Server).
2. Create the virtual directory for the Report Manager - Report Mngr is the web-based mgmt tool for setting up dirs + managing security on the web site.

choices:

1. enforce secure conns. to SSRS,
2. place SSRS virtual directories on the Web for Web deployment + reporting.

• SSRS allows 4 security lvls for comm. -> Report Server, from port 80 HTTP conn to SSL.
• For SSL conn, change RSReportServer.config -
• Also define IIS AUTH. MTHD FOR vIRTUAL dIRECTORY ACCESS - bASIC, wINDOWS, aNONYMOUS aCCESS. Windows Auth is recommneded for security, but Basic Auth. with SSL will provide secure connections from machines that are not logged onto your corporate domain.

Where SSRS access from Internet (e.g. for Internet deployment & rendering), prevent security risks:

• Report Server db should remain behind a firewall.

Create and configure SSRS instances by using the Reporting Services Configuration Manager tool

• For Server setup & management.
• SSRS server component includes RSCM tool, a UI admin tool for configuring SSRS server settings.
• If IIS installed, the following steps will enable instance for development & reporting:
• 1. Create virtual directories in IIS - The Web Svc that SSRS uses for report mgmt, publishing + rendering is located in Report Server Virtual Directory. (Def. name - Report Server).
• 2. Create the virtual directory for the Report Manager - Report Mngr is the web-based mgmt tool for setting up dirs + managing security on the web site.
• 3. Windows/Web Svc Identity - select local/domain acc/grp to run windows/asp.net svc.
• 4. Setup SSRS repository dbs - these dbs are used by SSRS to store rpt defs, data sources, virtual folder for Report Mngr. Database Setup -> select local/remote OR create new: db names - ReportServer, ReportServerTempDB.
• 5. Initialization - red X? - -> & click Initialize btn.
• 6. Start SSRS Instance - Server Status settingd pg, click Start to start svc. Verfiy instance running - connect to Report Manager virtual dir - http://localhost/reoprts
• 7. Backup encryption key - needed to recover contents of an installation. -> Encryption Keys pg - back up.

rsconfig.exe

• an admin command-line tool for managing SSRS server settings.
• manages the instance's conn. -> repository db (i.e. Report Server), + for setting up def. credentials for unattended rpt exec. (e.g. when triggered by an SSRS event such as a scheduled rpt) against dbs.

rskeymgmt.exe

• for mgmt of the encryption keys for ops such as backup, restore, create.
• SSRS uses symmetric encyption keys to secure & encrypt content in the Report Server db.

Install an SSRS infrastructure

Manage private keys for encryption

• rskeymgmt.exe - another command-line utility.
• used in mgmt of symmetric encrytion keys, that SSRS uses to secure + encrypt content in the Report Server db.
• used for general mgmt of encryption keys such as backup, restore, delete.
• Must be run locally - cannot be run against a remote svr.
• 
  
  
  
  
  

Chapter 1: Installing SQL Server 2005 BI Tools

Install SSAS
License terms - agree.
Software Prerquisities: .NF 2.0, SS Native Client. IIS for SSRS. Installer will prompt re. these + others.
System Config Check - config changes needed <>
Pdt key.
Instance name - name of this instance on machine. Instances apply to Database Engine, SSAS, SSRS.
Def. Instance - componentns sel. will be installed with no name => conn. strings only consist of server name.

Install multiple instances

• Setup.exe from Servers folder or installation setup files folder.
• Why mult. instances - diff. vers/editions on same physical machine. Mult. installations, each at diff svc pack lvls.
• Service Account - choose sys. security account under which to run each SQL service: a) customise for each svc acc., b) built-in - local (full access), network (limited access), c) domain user account d) start at end of setup (+ whenever server is started).

Install a clustered instance

• MSCS - Microsoft Clustering Services.
• SSAS is cluster-aware & can be installed in an MSCS installation.
• shared-nothing model - the drive volumes (ones that hold the db + cube data) can only be controlled by 1 machine at a time in the cluster.
• Automatic failover - if SQL Server service stops on 1 machine, the drives are fully controlled automatically by another in the cluster.
• nodes - servers in a cluster.
• failover instances - db instances/AS instances in an MSCS installation.
• 1. Install SSAS on a cluster node - SQL will recognise its env. and enable the option to install a failover instance. Check 'Create an AS Failover Cluster'. (option not available when installing on a non-cluster machine).
• 2. Instance Name - default or named.
• 3. For failover cluster instance, must provide a Virtual SQL Server name. \ - identifies node. This qualified server name will failover with the svc, and conn. strings will be able to access SSAS regardless of the node on which SSAS is running on.
• 4. Virtual Server Config pg - assign IP address for each subnet. IP addresses too failover. Virtual SQL Server name + IP address(es) must be unique on netwoerk. The virtual SQL Server name cannot be the same as the physical server name or the cluster name.
• Available Cluster Group - shows all available cluster groups into which you can install SSAS. MSCS groups resources that have dependencies & therefore need to failover together: drive volumes, server names, IP addresses. Only cluster groups with shared drives are available - SSAS requires data to be stored on a shared drive for clustering.
• Cluster Nodes Config Pg - chose nodes on the cluster on which SSAS can failover.
• Service Accounts - need to have same rights on all nodes of the cluster - thus must be a domain account.
• Domain Groups for Clustered Services - enter domain + grp that SSAS will use.

Install SSRS infrastructure

• When configured with def. settings on the def. instance, SSRS ready to use at end of install. You get: 2 dbs - ReportServer, ReportServer-TempDB (for catalog storage + data caching), 2 virtual servers in IIS - Reports, ReportServer - used for Report Manager Web Interface + SSRS Web Svc.
• If need 2nd instance name it (or use def. if prev. was named).
• Why 2 instances?: 1. if you have strict security reqs a 2nd instance allows separation of site-lvl security bet mult. groups of administrators. 2. if you have limited hardware for dev., test, & prod svrs, you can use mult. instances on a single server to simulate mutl. environments.
• Manual Configuration (~ Rep. Svcs Config Mnr tool).

Install multiple instances of SSRS

Practice Q's
Exercise - Install Prerequisites & Components

• IIS - check selected.
• Accept Licence, install prerequisites, Ssytem Config Check - correct if necessary.
• Enter name.
• Components to Install - 'Advanced' for installing Sample dbs + sample code & apps.
• Default instance.
• Service Account - built-in network service for limited access.
• Auth Mode - windows
• Collation Settings - default
• Report Server Installation Options pg - install def. config.
• During setup, whilst you can define a named instance, it will apply to all the features selected during that installation. To install different components with diff. named instances, you will need to run the installation multiple times, once for each unique instance name.
• SSAS - works only with Windows auth, & there fore does not have capability for mixed mode or SQL Server Auth.
• After Finish - configure Samples.
• install most recent svc pack - x86 == 32-bit sys, x64 == 64-bit sys. If you have multiple instances of db engine, SSRS, SSAS installed on single machine, you can selectively chose you can select instances to which svc packs will be applied. Tools & SSIS are not instance-aware.
• SSAS can only use MSCS with nodes that are connected to the drive subsystem.

Chapter 11, lesson 3: Using Declarative and Imp[erative Security to Protect Methods.

• CAS - declarative - compiler performs security checks prior to running code.
• CAS - imperative - code itself performs checks and controls what happens.
• SecurityAction.Demand, CodeAccessPermission.Demand() - checks an assm's caller for permissions.
• SecurityMnager.IsGranted() - checks whether an assm itself has particular CAS perms.

Techniques for Limiting Permissions

• use CAS assm decl to restrict perms granted to assm as a whole.
• Then control perms on a more granular lvl by restricting perms within mthd decl + using imperative stmts.
• EXAM HINT: use RequestRefuse(==Deny) and RequestOptional(==PermitOnly) for assm decl, and Deny and PermitOnly for mthds.
• Exception handling - revert to PermitOnly (say for logging) and then revert.

• Assert() - on a perm, it means that any code calling the mthd on which Assert is invoked is vouched for - it does not need said perm itself.

Chapter 11, lesson 2: Using Declarative Security to Protect Assemblies.

Because CAS can restric permissions given to an app., you must write apps to run in a partially trusted security context.

Use declarative CAS demands - ensure that assm has all necessary permissions but none that it does not require.

Reasons to use CAS Assm Decl

• To ensure that the runtime will never run your app without granting access to all required resources - if user attempts to run app and CAS security policy does not grant a required permission, an exception is thrown. Use SecurityAction.RequestMinimum - to declare all CAS perms required by app.
• To create a sandbox for app to ensure an attacker does not manipulae it to access unintended resources - use CAS decl to reduce your assms

Classes for CAS Permissions

• For each type of resource (e.g. files/folders, printers, network access,...) that can be restricted by CAS there is a .NF class.
• Permission Attribute classes - inherit from CodeAccessSecurityAttribute class.
• Action property - specifies how the runtime will interpret the permission. Set to...
• 1) SecurityAction.RequestMinimum - requires a perm. for assm to run. If assm lacks specified perm then runtime throws exception.
• 2) SecurityAction.RequestOptional -
• 3) SecurityAction.RequestRefuse -
• NOTE: CAS is significant only for partially trusted assmebles. The runtime completely ignores CAS decl for fully trusted assemblies.
• combine RequestMinimum and RequestOptional - for non-negotiable permissions for 1 thing, but nothing else required.
• UIPermission, Unrestricted = true - for debugging + RequestOptional.