SSRS – Set Report Height to Unlimited

Very often we need to let users view all the data in one page. This is especially true in the case of matrix. To do this, go to the Property sheet for Report, and set the Height under InteractiveSize to zero in.

SSIS – Call a Procedure in DB2

I try to avoid doing this. But sometimes, the data processing on DB2 side is too complicated, and it’s necessary to create a procedure on DB2 to do it. Then we need to call the procedure within a SSIS package.

First, create a variable to call the stored proc on DB2 as seen below:

Then create an Execute SQL Task to actually call that SSIS variable(as seen below..settings)

SSIS – Scheduling a SSIS Package in a SQL Job

If we need a config file.

If we need to embed credential in the connection string in the data source.

If we need to use Command Line (It’s an example where a SSIS package creates an Excel file.).

Or,

"D:\MSSQL2005\MSSQL$HSVR11 (x86)\90\DTS\Binn\DTExec.exe" /DTS "\MSDB\etl_Send_Excel" /SERVER lsvrname /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V

SSAS – Install Analysis Services

Here are the two databases for the sample data warehouse Adventure Works.

Here are the two cubes for the sample Adventure Works.

SSRS – Install Reporting Services (3)

You will get these 3 databases created.

On SQL Server Express.

SSAS – Install Analysis Services and Adventure Works on Vista

SQL Server 2005 Developer Edition – Installation Procedure

• Install Disc 1: install default instance
  • Database Services: SHERRY-PC
  • Analysis Services: SHERRY-PC
  • Reporting Services: SHERRY-PC\ReportServer
  • Notification Services
  • Integration Services: SHERRY-PC
  • SQLXML4
• Remove Workstation Component before install Disc 2
  • Go to Control Panel
  • Go to Program Features
  • Select SQL Server 2005
  • Click Uninstall
  • Select Workstation Component
• Install Disc 2: Tools. Install locally
  • Install Business Intelligence Development Studio (BIDS)
  • Install Management Studio
  • Install SQL XML4
  • Install Sample database
• Install SQL Server 2005 SP 2
  • Download from internet
  • Accept all defaults
• Install the AdventureWorks sample databases
  • AdventureWorks: OLTP
  • AdventureWorksDW: OLAP
  • Adventure Works DW: SSAS
  • Install after the Setup
  • From Add or Remove Programs, select Microsoft SQL Server 2005 and click Change. Follow the steps in the Microsoft SQL Server 2005 Maintenance wizard.
  • From Component Selection, select Workstation Components and then click Next.
  • From Welcome to the SQL Server Installation Wizard, click Next.
  • From System Configuration Check, click Next.
  • From Change or Remove Instance, click Change Installed Components.
  • From Feature Selection, expand the Documentation, Samples, and Sample Databases node.
  • Select Sample Code and Applications.
  • Expand Sample Databases and then select the sample databases to be installed. Click Next.
  • To install and attach the sample databases, from Sample Databases Setup, select Install and attach sample databases, and then click Next.
  • The database files are created and stored in the folder <drive>:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Data. The database is attached and ready for use.
  • Select the instance of SQL Server on which to install the sample databases and samples.
  • Complete the steps in the wizard.
  • To complete the installation of the samples, after setup, perform one of the following steps:
  • From the Start menu, click All Programs, click Microsoft SQL Server 2005, click Documentation and Tutorials, click Samples, and then click Microsoft SQL Server 2005 Samples.
• Now the above has installed Adventure Works Analysis Services Project in the folder "C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Analysis Services Project". There you will find 2 sub-folders: "Enterprise" and "Standard". If you have SQL Server Enterprise or Developer edition, then use project from Enterprise sub-folder as it has features available just in SQL Server Enterprise Edition.
• Tip for Windows Vista users: if you are doing installation from/on Windows Vista OS, copy sample project out of "c:\Program Files" folder to folder C:\Download\SQL SSAS Project\Enterprise. In Windows Vista this folder has extra security and you might not be able to save changes in project files as files will be read-only.
• In BIDS, open the above project, and examine the design, cubes, dimensions, etc. Then Build the application. This will create the Adventure Works DW.asdatabase file in folder C:\Download\SQL SSAS Project\Enterprise\bin
• Start à All Programs à SQL Server 2005 à Analysis Services à Deloyment Wizard. Use this wizard to deploy the Analysis Services database
• Open Management Studio, log on Analysis Services, we should see Adventure Works DW is a valid AS database.
• Now we can open a new query, start to write MDX.

SSRS – Installing & Configuring Reporting Services (2)

Report Manager (on Vista fro SQL Server 2005 Express)
http://sherry-pc/Reports$SQLExpress

Report Server (on Vista for SQL Server 2005 Express)
http://sherry-pc/ReportServer$SQLExpress

SSRS - How to install SQL Server 2005 Reporting Services

After finish installing the original release version of SQL Server 2005, we must apply SQL Server 2005 Service Pack 2 (SP2) or a later service pack.

Before installing SQL Server 2005 Reporting Services, I enabled Microsoft ASP.NET and Microsoft Internet Information Services (IIS). On Windows Vista, to enable ASP.NET and IIS, follow these steps:

In Control Panel, click Programs, and then double-click Programs and Features. Click Turn Windows features on or off. Then I basically turn on many features under Internet Information Services, Web Management Tools, and IIS 6 Management Compatibility, and under World Wide Web Services.

Then on Vista, do the following to start the World Wide Web Publishing service.

To configure the service to start automatically, follow these steps:

1. In Control Panel, click System and Maintenance.
2. Click Administrative Tools.
3. Double-click Services.
4. In the User Account Control dialog box, click Continue.
5. Right-click World Wide Web Publishing Service, and then click Properties.
6. In the World Wide Web Publishing Service Properties dialog box, click Automatic in the Startup Type list.
7. Click Apply, click Start, and then click OK.

Then start to install SQL Server 2005 with Advanced Services SP2.

After installation, we need to configure the reporting server. To do this, follow these steps:

1. Start the Reporting Services Configuration tool. To start the tool, click Start, point to All Programs, point to SQL Server 2005, point to Configuration Tools, and then click Reporting Services Configuration.
2. In the User Account Control dialog box, click Continue.
3. In the Report Server Installation Instance Selection dialog box, type the name of the computer in the Machine Name box.
4. Click the instance of SQL Server 2005 Reporting Services in the Instance Name list, and then click Connect.
5. Configure the server for operation.

When done, configure the report server for local administration. To access the report server and Report Manager locally, follow these steps on Vista:

1. Start Windows Internet Explorer.
2. On the Tools menu, click Internet Options.
3. Click Security.
4. Click Trusted Sites.
5. Click Sites.
6. Under Add this Web site to the zone, type http://ServerName. If you are not using HTTPS for the default site, click to clear the Require server certification (https:) for all sites in this zone check box.
7. Click Add.
8. Repeat to add the http://localhost URL, and then click Close.

Now we can start Internet Explorer together with the Run as administrator option. To do this, click Start, click All Programs, right-click Internet Explorer, and then click Run as administrator.

By default, the Report Manager URL is Http://ServerName/reports.

If you use SQL Server Express with Advanced Services SP2, the Report Manager URL is http://ServerName/reports$sqlexpress. If you use a named instance of Reporting Services, the Report Manager URL is http://ServerName/reports$InstanceName

SSIS – Delay Validation

Very often we need to delay the schema validation when we don’t want the validation performed. There might be a couple of reasons. One, our password has been changed. Two, there are branches on our steps, and the validation will fail for sure.

 

In the case, we should set the DelayValidation property to True at both the Connection level and each data flow level.

“Pivot” Table – Using CTE

Here is an example in SQL.

Recursive Query – Using CTE (Common Table Expression)

Here is a typical table that is designed to be recursive.

Using CTE, we can get all the child records, direct or indirect, that has a parent ID.

WITH SOME_HIERARCHY (SOME_ID, SOM_DEPT_ID, SOME_NM, SOME_DESC, PARENT_SOME_ID)
         AS   
         (    SELECT    SOME_ID, SOM_DEPT_ID, SOME_NM, SOME_DESC, PARENT_SOME_ID
            FROM    DB2SCHEMA.T_SOME_DEF  
            WHERE    PARENT_SOME_ID IN ( 384394939)
            UNION ALL
            SELECT    A.SOME_ID, A.SOM_DEPT_ID, A.SOME_NM, A.SOME_DESC, A.PARENT_SOME_ID
            FROM    DB2SCHEMA.T_SOME_DEF  AS A, SOME_HIERARCHY AS B
            WHERE    A.PARENT_SOME_ID = B.SOME_ID
         )
         SELECT * FROM SOME_HIERARCHY ORDER BY PARENT_SOME_ID, SOME_NM

information_schema - SQL-92 standard

I've known about the INFORMATION_SCHEMA views (or system tables) in SQL Server. Recently I also learned that they are actually part of the SQL-92 standard and supported on other database platforms.

The INFORMATION_SCHEMA views provide meta data information about the tables, columns, and other parts of your database. Because the structure of these tables are standardized you can write SQL statements that work on various database platforms.

You can  use INFORMATION_SCHEMA views to build some automatic tasks. With the INFORMATION_SCHEMA you can get the datatype, max character length, and if null values are allowed, and perform validation before it hits the database. And if a column is made wider, you don't have to make any code changes. Here is a simple example to drop a table if it exists in a procedure.

if exists
    (
        select *
        from information_schema.tables
        where table_name='daily_tbl'
        and table_schema='dbo'
    )
        drop table dbo.daily_tbl

Here is a list of the information schema views:

INFORMATION_SCHEMA.SCHEMATA
INFORMATION_SCHEMA.TABLES
INFORMATION_SCHEMA.COLUMNS
INFORMATION_SCHEMA.STATISTICS
INFORMATION_SCHEMA.USER_PRIVILEGES
INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
INFORMATION_SCHEMA.TABLE_PRIVILEGES
INFORMATION_SCHEMA.COLUMN_PRIVILEGES
INFORMATION_SCHEMA.CHARACTER_SETS
INFORMATION_SCHEMA.COLLATIONS
INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
INFORMATION_SCHEMA.ROUTINES
INFORMATION_SCHEMA.VIEWS
INFORMATION_SCHEMA.TRIGGERS
INFORMATION_SCHEMA.PROFILING

Send Error Email from a SSIS Package

Job Scheduling – Write to a Load Stat Table

Job Scheduling – Notify on Failure

Here is the script for the job notification.

Job Scheduling – Wait for a load stats

Here is the procedure that “waits for”.

Job Scheduling – Skip a step on Weekends

if(datename(dw, getdate()) in ('Sunday', 'Monday'))
    raiserror('it''s Sunday or Monday, skipping F report', 11, 1)

Incremental Loading

A typical design flow for incremental loading in ETL process.

The last step writes the loading status to a table via a procedure.

The procedure takes 2 parameters, and one of them is from a variable.

Use Row Count Data Flow Transformation in SSIS

Very often, we want to save the row count to a variable.

Use Send Mail Task as a Completion Message

Use a user variable to hold the dynamic message.

"Daily Dialer Load From IDS to SS Has Completed.

ACCOUNTS: " +  (DT_STR, 10, 1252) @[User::ROW_COUNT_CDA] + "
REPORT_APP_001: " + (DT_STR, 10, 1252) @[User::ROW_COUNT_RA0C] + "
REPORT_TAC_AGENT: " + (DT_STR, 10, 1252) @[User::ROW_COUNT_RTA] + "
I_HIST: " + (DT_STR, 10, 1252) @[User::ROW_COUNT_IHI] + "
I_HIST_D: " + (DT_STR, 10, 1252) @[User::ROW_COUNT_IHD]

Use Send Mail Task as Warning in SSIS

When the table is empty (or the variable is 2009-01-01), then we will perform the Send Mail task.

Use Dynamic Variable in SSIS

Date, or date range is very often needed for ETL process to bring data from host to SQL server. One way to do this is to use a dynamic variable in SSIS. The dynamic variable is first defined by querying the last load date, and then use the dynamic variable (will evaluated to a date or date range) in various other variables that define the actual queries on the host DBMS.

Access the Expression Builder from the variables Property sheet. This variable holds the actual query with the dynamic date.

"SELECT kkkkk,  SUBSTR(CAST( LOAD_TS AS CHAR(26) ), 1, 10) || ' ' || SUBSTR(CAST( LOAD_TS AS CHAR(26) ), 12, 2) || ':' || SUBSTR(CAST( LOAD_TS AS CHAR(26) ), 15, 2) || ':' || SUBSTR(CAST( LOAD_TS AS CHAR(26) ), 18, 9) AS LOAD_TS FROM xyz WHERE
LOAD_TS > '" + @[User::MAX_LOAD_TS] + "'"

A Sample of Matrix in SSRS

Some times, we need to “Pivot” our data to show data as a pivot table, instead of in tabular format. Here is an example. It runs total across and also vertically (at two different levels or groups). It also has ability to hide the details unless users click on the plus sign on the summary level.

Toggle Field Background Color in SSRS

Very often, we need to draw users’ attention to certain metrics in the report based on certain criteria. Here is the expression we can set for the Background color property for the field.

Toggle the Detail Group in SSRS

Very often, we want to give the users a summary first, with the detail group hidden initially until users click the plus sign on certain field on the summary group.

Show/Hide a Table based on a Report Parameter in SSRS

In the case where I have several tables, but only need to show a particular table based on users’ choice, I can set the Expression for the Visibility property for the table like this:

A Click-throug (drill-through) Solution in SSRS

When the detail sections are very different for each metrics, we normally use the click-through (or drill-through) feature in SSRS, instead of the drill-down feature.

A Template for RRSR Report

A RDL file template

Month End Date in DB2

Very often, we need to get the end of month date.

select ((CURRENT_DATE -(DAY(CURRENT_DATE) - 1) DAYS) + 1 MONTH - 1 DAY) AS MONTH_END_DT from sysibs.sysdummy1

Using Multi-value Parameters in SSRS Report

Often times we need to allow users to select multiple choices for a parameter from a SSRS report.

Create a dataset in the Visual Studio for the RDL file with stored procedure as data source.

And make sure the report parameter takes multi-value.

Then in the procedure, code it like this.

Here is the table-valued function ListToTable()

--==========================================
--CREATED DATE:    3/27/2009
--DESCRIPTION:    Takes a list and converts it to a two column table
--                For use when passing a list to a stored proc
--==========================================

ALTER FUNCTION [dbo].[ListToTable](@List as varchar(max), @Delim as varchar(10))
RETURNS @listTable table(Position int, Value varchar(8000))
AS
BEGIN
  DECLARE @myPos INT
  SET @myPos = 1

  WHILE charindex(@delim, @list) > 0
  BEGIN
    INSERT INTO @listTable(Position, Value)
    VALUES(@myPos, left(@list, charindex(@delim, @list) - 1))

    SET @myPos = @myPos + 1
    IF charindex(@delim, @list) = len(@list)
        INSERT INTO @listTable(Position, Value)
        VALUES (@myPos, '')
    SET @list = right(@list, len(@list) - charindex(@delim, @list))
  END

  IF len(@list) > 0
    INSERT INTO @listTable(Position, Value)
    VALUES (@myPos, @list)

  RETURN
END