Writting multiple rows from one input Row

Hi:

I'm trying to write a transformation script to write multiple rows from 1 input row. In SQL Server 2000 DTS I could do this by using DTSTransformStat_SkipFetch and DTSTransformStat_SkipRow. In SQL server 2005 it looks like I need to use EndOfRowset but I do not know how to use it for sure.

If this correct can someone show me a code example and if I am incorrect can someone tell me how to do this

Thanks

Answer this question

Writting multiple rows from one input Row

bjpohl
That is what I said to begin with. What I need to know is do I use SetEndRrow on an output buffer. Is this the correct process
Victor17

It completely depends on why you want to do this.

Have you looked at the multicast transform

-Jamie
turbofart

Like I said, you'll need a script component.

-Jamie
Phillipe
No problem. I appreciate the help. I'm afraid I still don't understand How do you tell it that you have out the information in the output buffer and need to write it out
haus_baus

Phil,

It must be because your output isn't called "Output0".

The name of the buffer is whatever you have called your output with the word "Buffer" concatenated onto it.

-Jamie
Qwavel

Jamie:

I am trying your code example but it doesn't recognize Output0Buffer.

Here is my code:

' Microsoft SQL Server Integration Services user script component

' This is your new script component in Microsoft Visual Basic .NET

' ScriptMain is the entrypoint class for script components

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Imports Microsoft.SqlServer.Dts.Pipeline

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim Count As Integer = 1

Output0Buffer.AddRow()

While Count < 8

Select Case Count

Case 1

Output0Buffer.oSubjectValue = "IOWA_COUNTY_CODE"

Output0Buffer.oSubjecttype = "ICC"

Case 2

Output0Buffer.oSubjectValue = "OR_HLTH_EMPLY_DEF"

Output0Buffer.oSubjecttype = "OHD"

Case 3

Output0Buffer.oSubjectValue = "OR_HLTH_EMPLR_TYPE"

Output0Buffer.oSubjecttype = "OHT"

Case 4

Output0Buffer.oSubjectValue = "IOWA_COUNTY_CODE"

Output0Buffer.oSubjecttype = "WPR"

Case 5

Output0Buffer.oSubjectValue = "NJ_SDI_UC_LIMIT"

Output0Buffer.oSubjecttype = "NJS"

Case 6

Output0Buffer.oSubjectValue = "COLO_WORKSITE_NUM"

Output0Buffer.oSubjecttype = "CWN"

Case 7

Output0Buffer.oSubjectValue = "HAWAII_ALT_SDI_PCT"

Output0Buffer.oSubjecttype = "HAS"

End Select

Count = Count + 1

End While

End Sub

End Class

I get the squiggly line under Output0Buffer.
Regerio R. Alcantara

Here is the transformation in 2000 DTS:

'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************

' Insert Multiple Destination Rows from 1 Source row
Function Main()
DTSGlobalVariables("counter").Value = DTSGlobalVariables("counter").Value + 1
Select Case(DTSGlobalVariables("counter").Value)
Case 1
subjectvalue = DTSSource("IOWA_COUNTY_CODE")
subjectype = "ICC"
Case 2
subjectvalue = DTSSource("OR_HLTH_EMPLY_DEF")
subjectype = "OHD"
Case 3
subjectvalue = DTSSource("OR_HLTH_EMPLR_TYPE")
subjectype = "OHT"
Case 4
subjectype = "WPR"
l_decpos = 0
l_string = cstr(DTSSource("WA_PENS_FUND_RATE"))
l_stringlen = len(l_string)
For i = 1 to l_stringlen
If Mid(l_string,i,1) = "." Then
l_decpos = i
End If
Next
Select Case l_decpos
Case 0
Select Case l_stringlen
Case 0
subjectvalue = ""
Case 1
subjectvalue = l_string+"0000"
Case 2
subjectvalue = l_string+"000"
Case 3
subjectvalue = l_string+"00"
Case 4
subjectvalue = l_string+"0"
Case 5
subjectvalue = l_string
End Select
Case 1
Select Case l_stringlen
Case 2
l_decimalpart = mid( l_string,l_decpos+1,1)+"0000"
subjectvalue = l_decimalpart
Case 3
l_decimalpart = mid( l_string,l_decpos+1,2)+"000"
subjectvalue = l_decimalpart
Case 4
l_decimalpart = mid( l_string,l_decpos+1,3)+"00"
subjectvalue = l_decimalpart
Case 5
l_decimalpart = mid( l_string,l_decpos+1,4)+"0"
subjectvalue = l_decimalpart
Case 6
l_decimalpart = mid( l_string,l_decpos+1,5)
subjectvalue = l_decimalpart
End Select
Case 2
Select Case l_stringlen
Case 3
l_decimalpart = mid( l_string,l_decpos+1,1)+"0000"
subjectvalue = l_decimalpart
Case 4
l_decimalpart = mid( l_string,l_decpos+1,2)+"000"
subjectvalue = l_decimalpart
Case 5
l_decimalpart = mid( l_string,l_decpos+1,3)+"00"
subjectvalue = l_decimalpart
Case 6
l_decimalpart = mid( l_string,l_decpos+1,4)+"0"
subjectvalue = l_decimalpart
Case 7
l_decimalpart = mid( l_string,l_decpos+1,5)
subjectvalue = l_decimalpart
End Select
Case Else

subjectvalue = ""
End Select

Case 5
subjectvalue = DTSSource("NJ_SDI_UC_LIMIT")
subjectype = "NJS"
Case 6
subjectvalue = DTSSource("COLO_WORKSITE_NUM")
subjectype = "CWN"
Case 7
subjectype = "HAS"
l_decpos = 0
subjectvalue = DTSSource("HAWAII_ALT_SDI_PCT")
End Select

If DTSGlobalVariables("counter").Value < 8 THEN
DTSDestination("PSIDIdentity")=DTSSource("PSIDIdentity")
DTSDestination("ProcessedPeriodIdentity") =DTSSource("ProcessedPeriodIdentity")
DTSDestination("StateSubjectValue")=subjectvalue
DTSDestination("StateSubjectType")=subjectype
Main =DTSTransformStat_SkipFetch
else
DTSGlobalVariables("counter").Value = 0
Main = DTSTransformStat_SkipRow
end if

End Function

Here is what the output table looks like:

3 PSIDIdentity int 4 0
2 ProcessedPeriodIdentity int 4 0
2 StateSubjectType char 3 0
0 StateSubjectValue varchar 30 0
0 ProcidDTTM datetime 8 0

And here is what the input SQLlooks like:

SELECT
A_IncomingPCS.PSIDIdentity,
A_IncomingPCS.ProcessedPeriodIdentity,
C_COSUBMST.HAWAII_ALT_SDI_PCT,
C_COSUBMST.COLO_WORKSITE_NUM,
C_COSUBMST.NJ_SDI_UC_LIMIT,
C_COSUBMST.WA_PENS_FUND_RATE,
C_COSUBMST.OR_HLTH_EMPLR_TYPE,
C_COSUBMST.OR_HLTH_EMPLY_DEF,
C_COSUBMST.IOWA_COUNTY_CODE,
C_COSUBMST.DATE_TIME_STAMP
FROM C_COSUBMST
INNER JOIN A_IncomingPCS
ON C_COSUBMST.PCS = A_IncomingPCS.IncomingPCS
AND C_COSUBMST.DATE_TIME_STAMP = A_IncomingPCS.DATE_TIME_STAMP
MadCat
Thanks Jamie. I really appreciate the help.
charlie.b

Aha, right. gotcha.

Officially, yes you do, in reality, no you don't. I asked about this once and was told told that this is done for you under the covers.

Sorry for the long drawn out thread.

-Jamie
mwong7025
I have and I do not believe it nor the conditionl split will work. I am reading a record from table 1. I read field 1 of the input record and write field one to the output record. based on what is in field on I set the value for field two of the output record. I then need to write the row to the output table and read field 2 of the input record. Field 2 then goes to field one of the output record and base on that I set the value for field 2 of the output record. I then read field 3 of the input record and write that to field one of the output record and the set the value for field 2 of the output record. So on and so forth. I will then have 3 rows in the output table for 1 row of input. All 3 rows have the same 2 fields with different data.
Glenn Altemose

Dan,

In this situation the script component will have to be asynchronous (because the number of output rows differs from the number of input rows). In an asynchronous script component you have have access to the output buffer which has a method called AddRow().

Rather than try and explain it - here's an example: https://blogs.conchango.com/jamiethomson/archive/2005/07/25/1841.aspx

-Jamie
Robert Rouse

I enountered the same problem (I think), and I never did come up with the answer myself, I found a workaround via changing my requirments.

But perhaps to help clarify, I think what Phil is asking is, how do you create multiple output rows from the one output buffer. I found no way to set the values, write it (which is what Phil I think is asking) and then modify the values and write again. To compare to ADO, how does one do a NewRow() in SSIS

(I'm still curious to find out the answer myself)

-Dan

Anoop.k

Phil026 wrote:
I have and I do not believe it nor the conditionl split will work. I am reading a record from table 1. I read field 1 of the input record and write field one to the output record. based on what is in field on I set the value for field two of the output record. I then need to write the row to the output table and read field 2 of the input record. Field 2 then goes to field one of the output record and base on that I set the value for field 2 of the output record. I then read field 3 of the input record and write that to field one of the output record and the set the value for field 2 of the output record. So on and so forth. I will then have 3 rows in the output table for 1 row of input. All 3 rows have the same 2 fields with different data.

Right, well I can't claim to completely understand the requirement (perhaps if you show us the input and the expected output it woud help) but it seems clear that this is a very specific scenario in which the out-of-the-box components won't do the job.

Happily SSIS provides the script component for scenarios such as these. It sounds as though yours will need to be an asynchronous component. The script component is basically provided to allow you to build custom transformation logic into a data-flow when the out-of-the-box components aren't enough.

There are plenty of resources about in regard to script components. Google is your friend, as is Donald Farmer's book.

-Jamie

P.S. Regardless of what I said above I dare say that a combination of the out-of-the-box components WILL do the job - but doing it in a script component might be easier.

PrabhatNigam

Just what I needed to know, thanks Jamie!
Writting multiple rows from one input Row

Answer this question