BIDS – Oracle to SQL Server – Fetch data into Variables

Background

Now that we have the foundation stuff out of the way, let us return to our original problem.

Tried to read data from an Oracle table into local variables defined in a BIDS package, but no go.

Lineage

Here are earlier posts.

  1. “Oracle Database 11g Express Edition” – Installation on Windows
    Link
  2. Oracle – SQLPlus – Create new User
    Link
  3. Oracle Database Client on Windows
    Link

Exercise

Package

Launched BIDS and created a new package.

 

DataSource

Connection Manager

Provider – Native OLE DB \ Microsoft OLE DB Provider for Oracle

Connection
Image

Detail
  1. Password
    • Please enter the password for the user you have been provisioned
  2. Persist Security Info
    • Yes
  3. User ID
    • The user provisioned for this activity
  4. Data Source
    • localhost
  5. Provider
    • MSDAORA.1
Test Connection
Image

Data Source Designer
Image

Control Flow

Execute SQL Task Editor

Execute SQL Task Editor – General

Image

 

Outline
  1. Options
    • Code page :- 1252
  2. Result Set
    • Result Set :- Single row
      • We are only expecting a single record from the database
  3. SQL Statement
    • Connection Type :- OLE DB
    • Connection :- localhost.msftbids
    • SQLSourceType :- Direct input
    • SQLStatement :- select USER as currentUser, SYSDATE as currentDate from DUAL
    • IsQueryStoredProcedure :- False
    • Bypassprepare :- False

 

Execute SQL Task Editor – General – Parse Query

Image

Outline

The query parsed correctly.

Execute SQL Task Editor – Parameter Parsing

Image

Outline

We do not have any parameters

 

Execute SQL Task Editor – Parameter Parsing

Image

Outline
Result Name Variable Name Variable Type
currentUser User::currentUser String
currentDate User::currentDate Datetime

 

Execute SQL Task Editor – Expressions

Image

Outline

— None defined —

 

Script Task Editor

Script Task Editor – General

Image

Outline

 

Script Task Editor – Script

Image

Script Variable Name Value
ScriptLanguage Script Language Microsoft Visual Basic .Net
PrecompileScriptIntoBinaryCode Indicate whether the script is pre-compiled into binary code True
OptimizeScriptExecution Indicate whether the script is pre-compiled into binary code True
EntryPoint Specifies the entry point ScriptMain
ReadOnlyVariables Comma separated list of variables to be made available for read access currentUser, currentDate
WriteOnlyVariables

 

Script Task Editor – Script

Outline
  1. To access the User Variables in “Script Task” script, we can choose a couple of methodologies
    • Dts.VariableDispenser
      • Dts.VariableDispenser.LockForRead(varName) // Dts.VariableDispenser.GetVariables
  2. We stole code from ZappySys
    • Title :- 3 Ways -SSIS Read Write Variables – Script Task C# / VB.net
    • Link
      Link
Code

 



' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Windows.Forms

Public Class ScriptMain

    'http://zappysys.com/forums/topic/ssis-read-write-variables-in-script-task-csharp-or-vb-net/
    '//Example:  Dim myVarValue As Object = ReadVariable("User::MyVar")
    Private Function ReadVariable(ByVal varName As String) As Object
        Dim result As Object
        Try
            Dim vars As Variables = Nothing
            Dts.VariableDispenser.LockForRead(varName)
            Dts.VariableDispenser.GetVariables(vars)
            Try
                result = vars(varName).Value
            Finally
                vars.Unlock()
            End Try
        Catch ex As Exception
            Throw ex '//Handle things your way or throw back
        End Try
        Return result
    End Function

	Public Sub Main()

        Dim strMessage As String
        Dim currentUserLocalVar As Object
        Dim currentDBDateLocalVar As Object


        'Read Variables into local Variable
        currentUserLocalVar = ReadVariable("User::currentUser")
        currentDBDateLocalVar = ReadVariable("User::currentDate")

        'Display Data using msgbox
        MsgBox(currentUserLocalVar.ToString(), MsgBoxStyle.Information, "currentUser")
        MsgBox(currentDBDateLocalVar.ToString(), MsgBoxStyle.Information, "currentDBDate")


        Dts.TaskResult = Dts.Results.Success

	End Sub

End Class

Execution

Source Code Control

GitHub

Link

 

Dedicated

Dedicate to ZappySys.

The code shared here was a lifesaver for me.

 

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s