July 22, 2008 at 1:52 pm
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
July 23, 2008 at 7:23 pm
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.
July 23, 2008 at 7:36 pm
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
July 23, 2008 at 8:44 pm
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.
July 23, 2008 at 8:55 pm
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
July 23, 2008 at 9:01 pm
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!
July 24, 2008 at 1:34 pm
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
July 24, 2008 at 5:13 pm
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
July 25, 2008 at 8:35 am
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
July 25, 2008 at 1:12 pm
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
July 28, 2008 at 4:08 pm
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
July 29, 2008 at 7:16 pm
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
July 29, 2008 at 9:12 pm
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
July 30, 2008 at 8:22 am
Thanks for the tip and happy to help!
Kay Batta
July 30, 2008 at 8:49 am
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