column map reference

  • let me explain you the whole thing again

    we have three tables in total

    one in oracle called application (source table)

    contains three columns with values

    appid appversion id appname

    1 0 sdf1

    1 1 sdf

    1 2 sdf

    2 0 ds

    2 1 ds

    3 0 sd

    etc

    we have two tables in Sql Server

    form1 (destination table)

    pkid

    now form1 has four fields

    form1id

    form1name

    parentform1id

    parentform1list

    the Pkid has two field with values

    pkid pkval

    form1id 2099

    now what i want to do is put the values in form1 table in such a way that when i am importing data from

    oracle

    appname goes to the formname

    2099 goes to form1id

    at this point i want to check application version id

    if it is 0

    parentform1id will be NULL

    parentform list will be same as formid

    now when the application version id changes with the same appid the form1 table should be

    form1id 2100

    formname appname

    parentform1id 2099

    parentformlist 2099

    for the third version

    form1id 2101

    formname appname

    parentform1id 2100

    parentformlist 2099

    now when the appid is changed and version again becomes 0 then form 1 table should look like

    form1id 2102

    formname appname

    parentform1id null

    parentformlist 2102

    for the second version of appid 2

    form1id 2103

    formname appname

    parentform1id 2102

    parentformlist 2102

    so the point is formid should check the values of the appid and version id and change its value from the pkid table according to it

    and appversion id starts with 0 and increment by 1

    does that make any sense

    thanx and please help

  • In the control flow task, get the starting value of pkval and put into a variable.

    Then call a data flow task.

    In the data flow task, select the rows you want from the application table and order them correctly.

    Add a script component to generate the values that will be fed into table form1.

    Any values that you want to preserve from one row to the next (such as previous row values) should be defined in Public Class ScriptMain. Values defined in a subroutine will be local to it and reset with each new row coming in.

    The script below is very minimal but should help you get started.

    One thing I could not figure out was how to make an integer field null. Perhaps someone else knows how.

    While I included logic to save the previous appid and appversionid, I did not use them. You will probably want to use them.

    The code below assumes that when appid changes, appversionid is always reset to 0.

    Imports System

    Imports System.Data

    Imports System.Math

    Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

    Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

    Public Class ScriptMain

    Inherits UserComponent

    Dim workpkval As Integer

    Dim prevappid As Integer

    Dim prevappversionid As Integer

    Dim workparentform1list As Integer

    'Capture the starting value in pkval before processing the first row.

    Public Overrides Sub PreExecute()

    workpkval = Variables.pkval

    workparentform1list = Variables.pkval

    MyBase.PreExecute()

    End Sub

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

    Row.form1id = workpkval

    Row.form1name = Row.appname

    If Row.appversionid = 0 Then

    'Row.parentform1id = null

    Row.parentform1list = workpkval

    workparentform1list = workpkval

    Else

    Row.parentform1id = workpkval - 1

    Row.parentform1list = workparentform1list

    End If

    workpkval = workpkval + 1

    prevappid = Row.appid

    prevappversionid = Row.appversionid

    End Sub

    End Class

    *** copied from data viewer ***

    appidĀ appversionid appname form1id form1name parentform1id parentform1list

    1 0 sdf1 2099 sdf1 0 2099

    1 1 sdf 2100 sdf 2099 2099

    1 2 sdf 2101 sdf 2100 2099

    2 0 ds 2102 ds 0 2102

    2 1 ds 2103 ds 2102 2102

    3 0 sd 2104 sd 0 2104

    You would send the output to table form1. I assume you would also want to update pkval in table pkid.

  • thats a great code but the problem is that the sql server table 'form' does not have the appid and appversion id fields. 'form' just checks that the version id and appid values are changing and it then changes the formid and parentid and parentformlist values accordingly to the same patterns. So is it possible that these values doesnt come in that table

  • The data viewer shows both the fields that were input to the script and the output fields. You would just pass the fields you need to the form1 table.

  • One more thing

    how to get the pkid value to initialize in the control flow diagram as pkid is a separate table and its value should be entered in the form1id field as the starting val

  • Do a SQL query where you select the value and write it to the variable. My SSIS book at home does not explain how to do this but I believe I have seen directions in other threads.

    Good luck!

  • Hello

    can you tell me how would i gonna update the pkid table as the logic behind this is that as the formid value is incremented after that the pkid value is incremented.

    besides the first entry of 2099

  • Make sure variable pkval has package scope. If not, delete it and add it on the Control Flow page.

    Add a string variable on the Control Flow page to hold the update query for pkval in form1. I called mine SQLUpdate.

    In the script component, blank out the ReadOnlyVariables.

    Replace the script with the following:

    Imports System

    Imports System.Data

    Imports System.Math

    Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

    Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

    Public Class ScriptMain

    Inherits UserComponent

    Dim workpkval As Integer

    Dim prevappid As Integer

    Dim prevappversionid As Integer

    Dim workparentform1list As Integer

    Public Overrides Sub PreExecute()

    workpkval = CType(ReadVariable("pkval"), Integer)

    workparentform1list = workpkval

    End Sub

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

    Row.form1id = workpkval

    Row.form1name = Row.appname

    If Row.appversionid = 0 Then

    'Row.parentform1id = null

    Row.parentform1list = workpkval

    workparentform1list = workpkval

    Else

    Row.parentform1id = workpkval - 1

    Row.parentform1list = workparentform1list

    End If

    workpkval = workpkval + 1

    prevappid = Row.appid

    prevappversionid = Row.appversionid

    End Sub

    Public Overrides Sub PostExecute()

    Dim wquery As String

    WriteVariable("pkval", workpkval)

    wquery = "update form1 set pkid = " + workpkval.ToString

    WriteVariable("SQLUpdate", wquery)

    End Sub

    'ReadVariable and WriteVariable are from a blog by Daniel Read from March 2007.

    'http://www.developerdotstar.com/community/node/512#comment-7903

    Private Function ReadVariable(ByVal varName As String) As Object

    Dim result As Object

    Try

    Dim vars As IDTSVariables90

    Me.VariableDispenser.LockForRead(varName)

    Me.VariableDispenser.GetVariables(vars)

    Try

    result = vars(varName).Value

    Catch ex As Exception

    Throw ex

    Finally

    vars.Unlock()

    End Try

    Catch ex As Exception

    Throw ex

    End Try

    Return result

    End Function

    Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)

    Try

    Dim vars As IDTSVariables90

    Me.VariableDispenser.LockForWrite(varName)

    Me.VariableDispenser.GetVariables(vars)

    Try

    vars(varName).Value = varValue

    Catch ex As Exception

    Throw ex

    Finally

    vars.Unlock()

    End Try

    Catch ex As Exception

    Throw ex

    End Try

    End Sub

    End Class

    ***

    The two subroutines I added give you much more control over the variables.

    On the Control Flow page, add an Execute SQL task after the Data Flow task. Change SQL Source Type to variable and list the variable in SQL Statement.

    You may have to experiment a bit with the correct format for the variable name. Some possibilities: SQLUpdate, @SQLUpdate, User::SQLUpdate.

    ***

    In case you are interested, the Script Task equivalent of the subroutines is:

    Public Sub Main()

    Dts.TaskResult = Dts.Results.Success

    'sample read

    'wCallType = CType(ReadVariable("CallType"), String)

    'sample write

    'WriteVariable("NumFolders", wNumFolders)

    End Sub

    'ReadVariable and WriteVariable are from a blog by Daniel Read from mid 2006.

    'http://www.developerdotstar.com/community/node/512/

    Private Function ReadVariable(ByVal varName As String) As Object

    Dim result As Object

    Try

    Dim vars As Variables

    Dts.VariableDispenser.LockForRead(varName)

    Dts.VariableDispenser.GetVariables(vars)

    Try

    result = vars(varName).Value

    Catch ex As Exception

    Throw ex

    Finally

    vars.Unlock()

    End Try

    Catch ex As Exception

    Dts.TaskResult = Dts.Results.Failure

    Throw ex

    End Try

    Return result

    End Function

    Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)

    Try

    Dim vars As Variables

    Dts.VariableDispenser.LockForWrite(varName)

    Dts.VariableDispenser.GetVariables(vars)

    Try

    vars(varName).Value = varValue

    Catch ex As Exception

    Dts.TaskResult = Dts.Results.Failure

    Throw ex

    Finally

    vars.Unlock()

    End Try

    Catch ex As Exception

    Throw ex

    End Try

    End Sub

  • thanks for the code.

    At the moment i am trying to figure out how to solve the null value problem in the parentformid column as it keeps changing all the time

  • just for the information

    the variable is typed as ? and then it is selected from the parameter list.

    But u said to use them in select statement. But im taking the oracle table in the select statement. How can i use this variable to update the PKID Table and where should i rite the sql statement becos the execute sql task doesnt support sql in the variable sql source

  • hello kbatta

    the code u sent me is great and it solved my problem

    but there is a problem.

    when i use the pkval variable as a string and write a sql statement in it to retrieve it does not convert it into integer using the CType casting.

    and give me error as

    conversion from string to type integer is not possible SSIS

    But when i use the variable type as a int with the fixed value it works fine

    I dont understand the problem. and also the update is not working. Please can you help

    thankz

  • I found an error in the script. Change the query build statement to:

    wquery = "update pkid set pkval = " + workpkval.ToString

    After the script component, I added a conditional split. If parentform1id is 0, send the row down the null parent path. Otherwise, send it down the nonnull parent path. They each go to an OLE DB destination where the table is form1. But in the null parent case on the mappings page, I deleted the line connecting parentform1id. This will cause it to load with a null.

    Back on the control flow, I added an execute SQL task after the data flow task. I set SQLSourceType to variable and SourceVariable to User::SQLUpdate.

    I also added an email task with @[User::SQLUpdate] in the Expressions MessageSource so I could see what the query was set to. The email said: update pkid set pkval = 2105

  • thanks kbatta for giving me the reply.

    Just for the records i found another way to make the value of the parentformid to null which is

    when u write a code for the assignment in the main method input0processbuffer

    set the value of parentformid_isnull=true in the condition and it will automatically make it a null value

    anyways thanks for the reply and hope to talk to you soon

  • Thanks for the tip and happy to help!

    Kay Batta

  • Dont mention it. I solved the problem through code

    But now i got into another problem. My problem is that

    Application table has child tables which contains the appid and appversionid. Same as form table has childs

    containing formid.

    Now i am not certain how to add values of the formid in the child based on the source appid and version id in the child which has to compare its values from the parent table and based on that assign child form the formid

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply