Click here to Skip to main content
15,894,041 members
Search within:


Convert VB script 2012 version to VB script 2017 in a script task
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
C#
VB
Visual-Studio
The SSIS upgrade package doesn't convert the script task properly and is not functional due to errors. Below is the code and need to convert to VB 2017 version.

VB
' 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

<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute>
<System.CLSCompliantAttribute(False)>
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum

    ' The execution engine calls this method when the task executes.
    ' To access the object model, use the Dts object. Connections, variables, events,
    ' and logging features are available as static members of the Dts class.
    ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    ' 
    ' To open Code and Text Editor Help, press F1.
    ' To open Object Browser, press Ctrl+Alt+J.

    Public Sub Main()
        '
        ' Add your code here
        '
        ' Generate Constraint Statement and Stored in Global Variable
        Call Generate_Query("= 'FDW01'", "FDW01", "LEDGER")
        Call Generate_Query("= 'FDW02'", "FDW02", "LEDGER")
        Call Generate_Query("= 'FDW03'", "FDW03", "LEDGER")
        Call Generate_Query("= 'FDW04'", "FDW04", "LEDGER")
        Call Generate_Query("= 'FDW05'", "FDW05", "LEDGER")
        Call Generate_Query("= 'FDW06'", "FDW06", "LEDGER")
        Call Generate_Query("= 'FDW07'", "FDW07", "LEDGER")
        Call Generate_Query("= 'FDW08'", "FDW08", "LEDGER")
        Call Generate_Query("= 'FDW09'", "FDW09", "LEDGER")
        Call Generate_Query("= 'FDW10'", "FDW10", "LEDGER")

        Call Generate_Query("IN ('FDW01', 'FDW02', 'FDW03', 'FDW04')", "FDWS01", "STATS")
        Call Generate_Query("IN ('FDW05', 'FDW06', 'FDW07')", "FDWS05", "STATS")
        Call Generate_Query("IN ('FDW08', 'FDW09', 'FDW10')", "FDWS10", "STATS")

        Call Generate_Query("", "FDWR01", "RATE")

        Dts.Variables("Cleanup_Query").Value = "EXEC S_FDW_GDSDM_Table_Clear 'T_TMP_ACTUALS_LOAD_FIN' "
        Dts.Variables("Cleanup_Query").Value = Dts.Variables("Cleanup_Query").Value.ToString() & "EXEC S_FDW_GDSDM_Table_Clear 'T_TMP_ACTUALS_LOAD_RATE' "

        Dts.Variables("Fiscal_Period").Value = Dts.Variables("FISCAL_YEAR").Value.ToString() & Dts.Variables("FISCAL_MONTH").Value.ToString()

        Dts.Variables("Load_SQL").Value = "EXEC S_FDW_GDS_Actual_Load_Control '"
        Dts.Variables("Load_SQL").Value = Dts.Variables("Load_SQL").Value.ToString() & Dts.Variables("LOAD_ID").Value.ToString() & "','"
        Dts.Variables("Load_SQL").Value = Dts.Variables("Load_SQL").Value.ToString() & Dts.Variables("Fiscal_Period").Value.ToString() & "','"
        Dts.Variables("Load_SQL").Value = Dts.Variables("Load_SQL").Value.ToString() & Dts.Variables("Fiscal_Period").Value.ToString() & "',"
        Dts.Variables("Load_SQL").Value = Dts.Variables("Load_SQL").Value.ToString() & "'T_TMP_ACTUALS_LOAD_FIN','"
        Dts.Variables("Load_SQL").Value = Dts.Variables("Load_SQL").Value.ToString() & Dts.Variables("LOAD_TYPE").Value.ToString() & "'"

        'MsgBox(Dts.Variables("Load_SQL").Value)

        Dts.TaskResult = ScriptResults.Success
    End Sub

    Private Sub Generate_Query(ByVal Partition As String, ByVal Query As String, ByVal Exec_Mode As String)

        Dim SQLStmt As String

        ' Prepare Data Pull Selection Columns
        If Exec_Mode = "LEDGER" Then
            SQLStmt = SQLStmt & "SELECT "
            SQLStmt = SQLStmt & " T1.FDW_LEGAL_ENTITY AS FDW_Legal_Entity "
            SQLStmt = SQLStmt & ",FDW_BOOKING_POINT   AS FDW_Booking_Point  "
            SQLStmt = SQLStmt & ",FDW_SERVICING_BRCH  AS FDW_Servicing_Branch "
            SQLStmt = SQLStmt & ",FDW_CHANNEL         AS FDW_Channel "
            SQLStmt = SQLStmt & ",FDW_LINE_ITEM       AS FDW_Line_Item "
            SQLStmt = SQLStmt & ",FDW_PRODUCT         AS FDW_Product  "
            SQLStmt = SQLStmt & ",FDW_CUSTOMER_GROUP  AS FDW_Customer_Group "
            SQLStmt = SQLStmt & ",FDW_RESIDENCY       AS FDW_Residency "
            SQLStmt = SQLStmt & ",FDW_CURRENCY        AS FDW_Currency "
            SQLStmt = SQLStmt & ",FDW_TRANSACT_LOC    AS FDW_Trans_Location "
            SQLStmt = SQLStmt & ",FDW_MRC             AS FDW_MRC "
            SQLStmt = SQLStmt & ",FDW_BUSINESS_EVENT  AS FDW_Business_Event "
            SQLStmt = SQLStmt & ",FDW_LOCAL_USE       AS FDW_Local_Use "
            SQLStmt = SQLStmt & ",FDW_VALUE_TYPE      AS FDW_Value_Type "
            SQLStmt = SQLStmt & ",T1.SOURCE           AS FDW_Source "
            SQLStmt = SQLStmt & ",REPEAT('0',4 - LENGTH(RTRIM(CAST(T1.FISCAL_YEAR as VARCHAR(4)))))"
            SQLStmt = SQLStmt & "||RTRIM(CAST(T1.FISCAL_YEAR as VARCHAR(4)))"
            SQLStmt = SQLStmt & "||REPEAT('0',2 - LENGTH(RTRIM(CAST(T1.FDW_FISCAL_MONTH as VARCHAR(2)))))"
            SQLStmt = SQLStmt & "||RTRIM(CAST(T1.FDW_FISCAL_MONTH as VARCHAR(2))) AS FDW_Fiscal_Period"
            SQLStmt = SQLStmt & ",T1.FDW_BALANCE     AS Balance_CAD "
            SQLStmt = SQLStmt & ",T1.FDW_ADB         AS Activity_CAD "
            SQLStmt = SQLStmt & ",T1.FDW_ENDBAL_BASE AS Balance_BASE "
            SQLStmt = SQLStmt & ",T1.FDW_AGGBAL_BASE AS Activity_BASE "
            SQLStmt = SQLStmt & ",T1.FDW_ENDBAL_SRC  AS Balance_SRC "
            SQLStmt = SQLStmt & ",T1.FDW_AGGBAL_SRC  AS Activity_SRC "
            SQLStmt = SQLStmt & ",T1.FDW_LOAD_ID     AS FDW_Load_ID "
            SQLStmt = SQLStmt & ",'FIN'               AS FDW_Table_Source "
            SQLStmt = SQLStmt & "FROM " & Dts.Variables("FDWSchema").Value.ToString() & ".PS_FDW_COA_LEDG_VW T1 "
        End If

        If Exec_Mode = "STATS" Then
            SQLStmt = SQLStmt & "SELECT "
            SQLStmt = SQLStmt & " T1.FDW_LEGAL_ENTITY AS FDW_Legal_Entity "
            SQLStmt = SQLStmt & ",FDW_BOOKING_POINT   AS FDW_Booking_Point  "
            SQLStmt = SQLStmt & ",FDW_SERVICING_BRCH  AS FDW_Servicing_Branch "
            SQLStmt = SQLStmt & ",FDW_CHANNEL         AS FDW_Channel "
            SQLStmt = SQLStmt & ",FDW_LINE_ITEM       AS FDW_Line_Item "
            SQLStmt = SQLStmt & ",FDW_PRODUCT         AS FDW_Product  "
            SQLStmt = SQLStmt & ",FDW_CUSTOMER_GROUP  AS FDW_Customer_Group "
            SQLStmt = SQLStmt & ",FDW_RESIDENCY       AS FDW_Residency "
            SQLStmt = SQLStmt & ",FDW_CURRENCY        AS FDW_Currency "
            SQLStmt = SQLStmt & ",FDW_TRANSACT_LOC    AS FDW_Trans_Location "
            SQLStmt = SQLStmt & ",FDW_MRC             AS FDW_MRC "
            SQLStmt = SQLStmt & ",FDW_BUSINESS_EVENT  AS FDW_Business_Event "
            SQLStmt = SQLStmt & ",FDW_LOCAL_USE       AS FDW_Local_Use "
            SQLStmt = SQLStmt & ",FDW_VALUE_TYPE      AS FDW_Value_Type "
            SQLStmt = SQLStmt & ",T1.SOURCE           AS FDW_Source "
            SQLStmt = SQLStmt & ",REPEAT('0',4 - LENGTH(RTRIM(CAST(T1.FISCAL_YEAR as VARCHAR(4)))))"
            SQLStmt = SQLStmt & "||RTRIM(CAST(T1.FISCAL_YEAR as VARCHAR(4)))"
            SQLStmt = SQLStmt & "||REPEAT('0',2 - LENGTH(RTRIM(CAST(T1.FDW_FISCAL_MONTH as VARCHAR(2)))))"
            SQLStmt = SQLStmt & "||RTRIM(CAST(T1.FDW_FISCAL_MONTH as VARCHAR(2))) AS FDW_Fiscal_Period"
            SQLStmt = SQLStmt & ",T1.FDW_BALANCE     AS Balance_CAD "
            SQLStmt = SQLStmt & ",T1.FDW_ADB         AS Activity_CAD "
            SQLStmt = SQLStmt & ",T1.FDW_ENDBAL_BASE AS Balance_BASE "
            SQLStmt = SQLStmt & ",T1.FDW_AGGBAL_BASE AS Activity_BASE "
            SQLStmt = SQLStmt & ",T1.FDW_ENDBAL_SRC  AS Balance_SRC "
            SQLStmt = SQLStmt & ",T1.FDW_AGGBAL_SRC  AS Activity_SRC "
            SQLStmt = SQLStmt & ",T1.FDW_LOAD_ID     AS FDW_Load_ID "
            SQLStmt = SQLStmt & ",'STAT'               AS FDW_Table_Source "
            SQLStmt = SQLStmt & "FROM " & Dts.Variables("FDWSchema").Value.ToString() & ".PS_FDW_COA_STAT_VW T1 "
        End If

        If Exec_Mode = "RATE" Then
            SQLStmt = SQLStmt & "SELECT "
            SQLStmt = SQLStmt & " T1.FDW_LEGAL_ENTITY AS FDW_Legal_Entity "
            SQLStmt = SQLStmt & ",FDW_BOOKING_POINT   AS FDW_Booking_Point  "
            SQLStmt = SQLStmt & ",FDW_SERVICING_BRCH  AS FDW_Servicing_Branch "
            SQLStmt = SQLStmt & ",FDW_CHANNEL         AS FDW_Channel "
            SQLStmt = SQLStmt & ",FDW_LINE_ITEM       AS FDW_Line_Item "
            SQLStmt = SQLStmt & ",FDW_PRODUCT         AS FDW_Product  "
            SQLStmt = SQLStmt & ",FDW_CUSTOMER_GROUP  AS FDW_Customer_Group "
            SQLStmt = SQLStmt & ",FDW_RESIDENCY       AS FDW_Residency "
            SQLStmt = SQLStmt & ",FDW_CURRENCY        AS FDW_Currency "
            SQLStmt = SQLStmt & ",FDW_TRANSACT_LOC    AS FDW_Trans_Location "
            SQLStmt = SQLStmt & ",FDW_MRC             AS FDW_MRC "
            SQLStmt = SQLStmt & ",FDW_BUSINESS_EVENT  AS FDW_Business_Event "
            SQLStmt = SQLStmt & ",FDW_LOCAL_USE       AS FDW_Local_Use "
            SQLStmt = SQLStmt & ",FDW_VALUE_TYPE      AS FDW_Value_Type "
            SQLStmt = SQLStmt & ",T1.SOURCE           AS FDW_Source "
            SQLStmt = SQLStmt & ",REPEAT('0',4 - LENGTH(RTRIM(CAST(T1.FISCAL_YEAR as VARCHAR(4)))))"
            SQLStmt = SQLStmt & "||RTRIM(CAST(T1.FISCAL_YEAR as VARCHAR(4)))"
            SQLStmt = SQLStmt & "||REPEAT('0',2 - LENGTH(RTRIM(CAST(T1.FDW_FISCAL_MONTH as VARCHAR(2)))))"
            SQLStmt = SQLStmt & "||RTRIM(CAST(T1.FDW_FISCAL_MONTH as VARCHAR(2))) AS FDW_Fiscal_Period"
            SQLStmt = SQLStmt & ",0.0     AS Balance_CAD "
            SQLStmt = SQLStmt & ",T1.FDW_ACTIVITY         AS Activity_CAD "
            SQLStmt = SQLStmt & ",0.0 AS Balance_BASE "
            SQLStmt = SQLStmt & ",0.0 AS Activity_BASE "
            SQLStmt = SQLStmt & ",0.0  AS Balance_SRC "
            SQLStmt = SQLStmt & ",0.0  AS Activity_SRC "
            SQLStmt = SQLStmt & ",T1.FDW_LOAD_ID     AS FDW_Load_ID "
            SQLStmt = SQLStmt & ",'RATE'               AS FDW_Table_Source "
            SQLStmt = SQLStmt & "FROM " & Dts.Variables("FDWSchema").Value.ToString() & ".PS_FDW_COA_RATE_VW T1 "
        End If

        SQLStmt = SQLStmt & " WHERE "

        If Exec_Mode <> "RATE" Then
            SQLStmt = SQLStmt & " T1.FDW_PART_KEY " & CStr(Partition) & " AND "
        End If

        SQLStmt = SQLStmt & " T1.FDW_VALUE_TYPE = 'FDWACTM' "
        SQLStmt = SQLStmt & " AND T1.FDW_FISCAL_MONTH = " & Dts.Variables("FISCAL_MONTH").Value.ToString()
        SQLStmt = SQLStmt & " AND T1.FISCAL_YEAR = " & Dts.Variables("FISCAL_YEAR").Value.ToString()
        SQLStmt = SQLStmt & " WITH UR "

        ' Data Pull Criteria
        Dts.Variables(CStr(Query)).Value = SQLStmt



    End Sub

End Class









Errors:

Severity	Code	Description	Project	File	Line	Suppression State
Error	BC30910	'ScriptMain' cannot inherit from class 'Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase' because it expands the access of the base class outside the assembly.	ST_f3c33abeabc4413d8fd28dabcddc1a7c	C:\Users\KAMDAA2\AppData\Local\Temp\Vsta\SSIS_ST140\VstaMTznTpDubkyJsMNGerUsVA\VstaDIX04Lo5i0WQREgLQlRXuA\ScriptMain.vb	13	Active



Severity	Code	Description	Project	File	Line	Suppression State
Error	BC30456	'Runtime' is not a member of 'Dts'.	ST_f3c33abeabc4413d8fd28dabcddc1a7c	C:\Users\KAMDAA2\AppData\Local\Temp\Vsta\SSIS_ST140\VstaMTznTpDubkyJsMNGerUsVA\VstaDIX04Lo5i0WQREgLQlRXuA\ScriptMain.vb	16	Active



Severity	Code	Description	Project	File	Line	Suppression State
Error	BC30451	'Dts' is not declared. It may be inaccessible due to its protection level.	ST_f3c33abeabc4413d8fd28dabcddc1a7c	C:\Users\KAMDAA2\AppData\Local\Temp\Vsta\SSIS_ST140\VstaMTznTpDubkyJsMNGerUsVA\VstaDIX04Lo5i0WQREgLQlRXuA\ScriptMain.vb	50	Active



Severity	Code	Description	Project	File	Line	Suppression State
Message	IDE0054	Use compound assignment	ST_f3c33abeabc4413d8fd28dabcddc1a7c	C:\Users\KAMDAA2\AppData\Local\Temp\Vsta\SSIS_ST140\VstaMTznTpDubkyJsMNGerUsVA\VstaDIX04Lo5i0WQREgLQlRXuA\ScriptMain.vb	73	Active


What I have tried:

Used SSIS upgrade tool to no avail.
Posted
Updated 23-Jun-21 20:57pm
v2
Add a Solution

1 solution

Follow the instructions from MSDN[^]

Quote:

To correct this error



* Change the access level of the derived class or interface to be at least as restrictive as that of the base class or interface.

-or-

* If you require the less restrictive access level, remove the Inherits statement. You cannot inherit from a more restricted base class or interface.
 
Share this answer
 
Posted
Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month
Related Questions
Convert vb script into C# script in ssis
Converting VB Script to C# for Script Task in SSIS 2008
Script error show when sign in
creating bach script or vb script for scheduling
How do I convert Python script to java
Replace in VB Script
We want to convert our ms SQL script to my SQL script
VB Script code
Need to modify Python script
VB Script to automate IE settings

Advertise
Privacy
Cookies
Terms of Use
Last Updated 24 Jun 2021
Layout: fixed | fluid
Copyright © CodeProject, 1999-2024
All Rights Reserved.

Web01 2.8:2024-04-02:1

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900