Category: Oracle Database

Posted on

Quick Fixes on SSIS with Oracle Data Sources

SSIS logo

I’m currently in the process of building out an ODS (Operational Data Store) that will integrate data from multiple systems and make that data available for reporting using Spotfire and other BI tools.

Both the ODS DBMS and the first system that will be used as a source for the data in the ODS run on Oracle Database. I am using Visual Studio Ultimate 2013 64-bit to build an SSIS package to flow the data from the source into the ODS.

In debugging the SSIS package, I’ve run into a few things that make troubleshooting much easier. The first thing to know is that the Oracle Database (in this case) is a 32-bit system. The first thing that must be done after loading the SSIS package into Visual Studio is making sure that the Run64BitRuntime setting is set to False in the project’s Properties box. (This can be found in the PROJECT menu.)

Since the data sources for the ODS are Oracle, the SQL queries used to pull data must conform to Oracle syntax. To prevent things like SSIS automatically adding semicolons to the queries (and preventing me from adding them myself), I set the BypassPrepare property to True. This should allow the query to run the same way in SSIS as it does in SQL Developer. (If this is set to False, SSIS will parse the query rather than passing it to Oracle.) This setting made writing queries much easier, as I could test them in SQL Developer and paste them into SSIS without having to rewrite them to conform to SSIS.

Posted on

Spawning a New Window for Each CrystalReportViewer

logos for Visual Studio and Crystal Reports

Update to this post: I have found a bug in the CrystalReportViewer that I address here.

In creating a ASP.NET Web Forms site for viewing Crystal Reports, I needed to open multiple dates for the same report to compare values. The previous setup I used would not allow this; only one report could be open at a time. To remedy this, I changed the way reports would be loaded, and made a separate window for each one.

Excerpt of the home page code:

<table style="border-width:0px; border-spacing:1px; border-collapse:collapse;">
    <tr style="vertical-align:top;">
        <td class="fcel1">
            
<strong>Report</strong>
                <asp:DropDownList id="ddlReports" runat="server" AutoPostBack="True" />
            
        </td>
       <asp:panel id="pnlreportdate" runat="server">
            <td class="fcel1">
                
<strong>Report Date</strong>
                    <asp:TextBox id="txtSingleDate" runat="server"></asp:TextBox>
                    <ajaxToolkit:CalendarExtender
                        ID="CalendarExtender1" runat="server" TargetControlID="txtSingleDate">
                    </ajaxToolkit:CalendarExtender>
                
            </td>
        </asp:panel>
        <asp:panel id="pnlstartenddates" runat="server">
            <td class="fcel1">
                
<strong>Begin Date</strong>
                    <asp:TextBox id="txtBeginDate" runat="server"></asp:TextBox>
                    <ajaxToolkit:CalendarExtender
                        ID="CalendarExtender2" runat="server" TargetControlID="txtBeginDate">
                    </ajaxToolkit:CalendarExtender>
                
            </td>
            <td class="fcel1">
                
<strong>End Date</strong>
                    <asp:TextBox id="txtEndDate" runat="server"></asp:TextBox>
                    <ajaxToolkit:CalendarExtender
                        ID="CalendarExtender3" runat="server" TargetControlID="txtEndDate">
                    </ajaxToolkit:CalendarExtender>
                
            </td>
        </asp:panel>
        <td class="fcel1" style="vertical-align:middle;">
            <asp:Button id="btnGenerate" runat="server" Text="Generate"></asp:Button>
        </td>
    </tr>
</table>

…and its code behind:

Imports AjaxControlToolkit
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Imports CrystalDecisions.Web
Imports System.IO

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Init(sender As Object, e As System.EventArgs) Handles Me.Init
        If Not Page.IsPostBack Then
            txtSingleDate.Text = Now.Date()
            txtBeginDate.Text = Now.Date()
            txtEndDate.Text = Now.Date()
        End If
    End Sub

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If Not Page.IsPostBack Then
            Dim dirpath As String = Utilities.AppSettingsFunction.getValue("ReportFolder")
            Dim thedir As DirectoryInfo = New DirectoryInfo(Server.MapPath(dirpath))
            Dim filenames As FileInfo() = thedir.GetFiles()

            ddlReports.DataSource = filenames
            ddlReports.DataBind()

            For Each report As ListItem In ddlReports.Items
                Dim reportFileName As String = report.Value
                Dim reportText As String = reportFileName

                reportText = Replace(reportText, ".rpt", "")

                report.Text = reportText
            Next
        End If
        ShowPanels()
    End Sub

    Protected Function SetParameters() As String
        Dim strOnClientClick As String
        Dim BeginDate As String
        Dim EndDate As String
        Dim SingleDate As String
        Dim RptName As String

        BeginDate = txtBeginDate.Text
        EndDate = txtEndDate.Text
        SingleDate = txtSingleDate.Text
        RptName = ddlReports.SelectedValue
        strOnClientClick = "window.open('Pages/Reports.aspx?guid=" & newGuid().ToString & "&rpt=" & RptName
        If pnlstartenddates.Visible Then
            strOnClientClick += "&begin=" & BeginDate & "&end=" & EndDate
        ElseIf pnlreportdate.Visible Then
            strOnClientClick += "&single=" & SingleDate
        End If
        strOnClientClick += "','_blank');"
        Return strOnClientClick
    End Function

    Protected Sub ShowPanels()
        Dim strSelectedValue As String
        Dim bSingleDate As Boolean
        Dim bMultiDate As Boolean

        bSingleDate = False
        bMultiDate = False

        strSelectedValue = ddlReports.SelectedValue.ToString()

        Dim arrSingleDate() As String = Utilities.AppSettingsFunction.getValue("SingleDate").Split(",")
        Dim arrMultiDate() As String = Utilities.AppSettingsFunction.getValue("MultiDate").Split(",")

        If Array.IndexOf(arrSingleDate, strSelectedValue) <> -1 Then
            bSingleDate = True
            bMultiDate = False
        ElseIf Array.IndexOf(arrMultiDate, strSelectedValue) <> -1 Then
            bSingleDate = False
            bMultiDate = True
        End If

        pnlreportdate.Visible = bSingleDate
        pnlstartenddates.Visible = bMultiDate
    End Sub

    Protected Function newGuid() As Guid
        Dim guid As Guid
        guid = guid.NewGuid()
        Return guid
    End Function

    Protected Sub btnGenerate_Click(sender As Object, e As System.EventArgs) Handles btnGenerate.Click
        Dim strOnClientClick As String = SetParameters()
        Page.ClientScript.RegisterStartupScript(Me.GetType(), newGuid().ToString(), strOnClientClick, True)
    End Sub
End Class

The reports.aspx page that is used for all reports:

<%@ Page Language="VB" AutoEventWireup="true" CodeFile="Reports.aspx.vb" Inherits="Pages_Reports" %>

<%@ Register Assembly="CrystalDecisions.Web, Version=13.0.2000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"     Namespace="CrystalDecisions.Web" TagPrefix="CR" %>

<meta http-equiv="X-UA-Compatible" content="IE=9,chrome=1" />

</pre>
<form id="form1">
<div>

</div>
</form>
<pre>

…and the code behind for reports.aspx:

Imports CrystalDecisions.Shared
Imports System.IO
Imports CrystalDecisions.CrystalReports.Engine

Partial Class Pages_Reports
    Inherits System.Web.UI.Page

    Protected strQueryString As String

    Protected Sub Page_Init(sender As Object, e As System.EventArgs) Handles Me.Init
        strQueryString = Request.QueryString.ToString()
        Page.Title = Replace(Request.QueryString("rpt").ToString(), ".rpt", "")
        If Not Page.IsPostBack Then
            'Do nothing
        ElseIf Session(strQueryString) IsNot Nothing Then
            Me.CrystalReportViewer1.ReportSource = Session(strQueryString)
            CrystalReportViewer1.DataBind()
            UpdatePanel1.Update()
        End If
    End Sub

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If Not Page.IsPostBack Then
            Dim strReportName As String = Request.QueryString("rpt")
            Dim strSingleDate As String = Request.QueryString("single")
            Dim strBeginDate As String = Request.QueryString("begin")
            Dim strEndDate As String = Request.QueryString("end")

            Dim bSingleDate As Boolean = False
            Dim bMultiDate As Boolean = False

            Dim arrSingleDate() As String = Utilities.AppSettingsFunction.getValue("SingleDate").Split(",")
            Dim arrMultiDate() As String = Utilities.AppSettingsFunction.getValue("MultiDate").Split(",")

            If Array.IndexOf(arrSingleDate, strReportName) <> -1 Then
                bSingleDate = True
                bMultiDate = False
            ElseIf Array.IndexOf(arrMultiDate, strReportName) <> -1 Then
                bSingleDate = False
                bMultiDate = True
            End If

            Dim connInfo As New ConnectionInfo
            Dim rptDoc As New ReportDocument

            'setup the connection
            connInfo = GetConnectionInfo()

            'load the Crystal Report
            rptDoc.Load(Server.MapPath( _
                  Utilities.AppSettingsFunction.getValue("ReportFolder") _
                  & strReportName))

            'add required parameters
            If bSingleDate Then
                rptDoc.SetParameterValue("REPORT_DATE", strSingleDate & " 23:59:59")
            End If

            If bMultiDate Then
                rptDoc.SetParameterValue("BEGIN_DATE", strBeginDate & " 00:00:00")
                rptDoc.SetParameterValue("END_DATE", strEndDate & " 23:59:59")
            End If

            'apply logon information
            For Each tbl As CrystalDecisions.CrystalReports.Engine.Table In rptDoc.Database.Tables
                Dim repTblLogonInfo As TableLogOnInfo = tbl.LogOnInfo
                repTblLogonInfo.ConnectionInfo = connInfo
                tbl.ApplyLogOnInfo(repTblLogonInfo)
            Next

            'Set, bind, and display Crystal Reports Viewer data source
            Session(strQueryString) = rptDoc
            Me.CrystalReportViewer1.ReportSource = Session(strQueryString)
            CrystalReportViewer1.DataBind()
        ElseIf Session(strQueryString) IsNot Nothing Then
            'Dim rptView As CrystalReportViewer = FindControl("rptViewer_" & report.Text)
            Me.CrystalReportViewer1.ReportSource = Session(strQueryString)
            UpdatePanel1.Update()
        End If
    End Sub

    Protected Function GetConnectionInfo() As ConnectionInfo
        Dim connInfo As New ConnectionInfo

        'setup the connection
        With connInfo
            .ServerName = Utilities.AppSettingsFunction.getValue("ServerName_" & Utilities.AppSettingsFunction.getValue("Environment"))
            .DatabaseName = ""
            .UserID = Utilities.AppSettingsFunction.getValue("UserID_" & Utilities.AppSettingsFunction.getValue("Environment"))
            .Password = Utilities.AppSettingsFunction.getValue("Password_" & Utilities.AppSettingsFunction.getValue("Environment"))
        End With
        Return connInfo
    End Function
End Class
Posted on

Using Excel VBA to Copy an ADODB Recordset to Multiple Worksheets

Microsoft VBA logo

In adding functionality to an ancient Excel VBA reporting app for an Oracle database, I decided to set up a temporary worksheet to use as a place to store data before copying it to multiple other worksheets rather than copying directly from the recordset multiple times.

Basically, this VBA procedure takes an ADODB recordset and a Boolean value as parameters. The recordset is the data source, and the Boolean indicates whether or not the data will be copied to a particular worksheet. The temporary worksheet is cleared before the recordset is copied into it, and is cleared again after the data is copied to the other worksheet(s).

Public Sub CopyToSheets(ByRef rs As ADODB.Recordset, ByVal bCopyToSecondSht As Boolean)
    Dim MainSht As Worksheet, TempSht As Worksheet, Rng As Range, c As Range, lastRow As Long, intNextRow As Integer

    Set MainSht = ActiveWorkbook.Worksheets(cMainSht)
    Set TempSht = ActiveWorkbook.Worksheets(cTempData)

    TempSht.Cells.Clear

    rs.Open
    TempSht.Cells(1, 1).CopyFromRecordset rs
    rs.Close

    lastRow = TempSht.Range("A" & Rows.count).End(xlUp).Row
    Set Rng = TempSht.Range("A1:A" & lastRow)
    For Each c In Rng
        intNextRow = NextRowNumber(MainSht)
        c.EntireRow.Copy MainSht.Cells(intNextRow, 1)
        If bCopyToSecondSht Then
            Dim SecondSht As Worksheet
            Set SecondSht = ActiveWorkbook.Worksheets(cSecondSht)
            intNextRow = NextRowNumber(SecondSht)
            c.EntireRow.Copy SecondSht.Cells(intNextRow, 1)
        End If
    Next c
    TempSht.Cells.Clear
End Sub