Need to generate multiple output rows from single input row

Need to generate multiple output rows from single input row

Post by Alfred Akit » Fri, 14 Apr 2000 04:00:00



I am trying to generate multiple output rows from a single input row using a
DTS ActiveX script.  The script accepts a single row (with columns for a
start and end date), then generates a row for each day within that range to
a date dimension table.  Although I can do this with VB, I am hoping that I
can simply us DTS.  I have a <<Do while  ... Loop>> that generates each
output row.  The loop ends with the statement <<  Main =
DTSTransformStat_SkipFetch  >>.  Main is the ActiveX function and I expect
the DTSTransformStat_SkipFetch  status to cause the data pump to write out
the current output and simply use the current input for the next
transformation.  The ActiveX script loops through as expected but writes out
only the last transformation.

Anyone knows what I am doing wrong?  I appreciate any and all help.

 
 
 

Need to generate multiple output rows from single input row

Post by Neil Clayto » Sat, 15 Apr 2000 04:00:00


The way DTS works means that the loop is outside the ActiveX script.
Setting the DTSTransformStat_SkipFetch flag is effectively saying 'go back
to the start of the loop'.

So what you need is to keep a loop variable (for the number of days that you
want the loop to execute for) that will maintain its value between calls to
your ActiveX script. You do this by storing it as a Global Variable.

Goto the Package Properties dialog and add a Global vaiable of type Integer
and set its initial value to 0.  Then your looping code should look
something like this:

option explicit
Function Main
Dim iLoop

    iLoop = DTSGlobalVariables("LoopVar").Value
    DTSDestination("OutputDate").Value = DateAdd("d", iLoop,
DTSSource("SourceDate").Value)
    if iLoop = 6 then
        DTSGlobalVariables("LoopVar").Value = 0
        Main = DTSTransformStat_OK
    else
        DTSGlobalVariables("LoopVar").Value = iLoop + 1
        Main = DTSTransformStat_OK + DTSTransformStat_SkipFetch
    end if
end function

Hope this helps,

Neil


>I am trying to generate multiple output rows from a single input row using
a
>DTS ActiveX script.  The script accepts a single row (with columns for a
>start and end date), then generates a row for each day within that range to
>a date dimension table.  Although I can do this with VB, I am hoping that I
>can simply us DTS.  I have a <<Do while  ... Loop>> that generates each
>output row.  The loop ends with the statement <<  Main =
>DTSTransformStat_SkipFetch  >>.  Main is the ActiveX function and I expect
>the DTSTransformStat_SkipFetch  status to cause the data pump to write out
>the current output and simply use the current input for the next
>transformation.  The ActiveX script loops through as expected but writes
out
>only the last transformation.

>Anyone knows what I am doing wrong?  I appreciate any and all help.


 
 
 

Need to generate multiple output rows from single input row

Post by Mike Khayzniko » Sat, 15 Apr 2000 04:00:00


Hi!

You can use SQL to generate multiple rows from the single row. You will need
a CALENDAR table, containing all possible dates. You can create it with a
simple loop SQL-script. The query will  look something like this

select DATA.*
from DATA, CALENDAR
where CALENDAR.DATE between DATA.START_DATE and DATA.END_DATE

Now every record from DATA is repeated as many times as there are records in
CALENDAR for the interval between START_DATE and END_DATE.

best regards,
--
----------------------------------------------------------------------------
-------------------------
Mike Khayznikov
http://mkhaiz.euro.ru


Quote:> I am trying to generate multiple output rows from a single input row using
a
> DTS ActiveX script.  The script accepts a single row (with columns for a
> start and end date), then generates a row for each day within that range
to
> a date dimension table.  Although I can do this with VB, I am hoping that
I
> can simply us DTS.  I have a <<Do while  ... Loop>> that generates each
> output row.  The loop ends with the statement <<  Main =
> DTSTransformStat_SkipFetch  >>.  Main is the ActiveX function and I expect
> the DTSTransformStat_SkipFetch  status to cause the data pump to write out
> the current output and simply use the current input for the next
> transformation.  The ActiveX script loops through as expected but writes
out
> only the last transformation.

> Anyone knows what I am doing wrong?  I appreciate any and all help.

 
 
 

Need to generate multiple output rows from single input row

Post by Alfred Akit » Sat, 15 Apr 2000 04:00:00


It worked!!  I thank you very much.


> The way DTS works means that the loop is outside the ActiveX script.
> Setting the DTSTransformStat_SkipFetch flag is effectively saying 'go back
> to the start of the loop'.

> So what you need is to keep a loop variable (for the number of days that
you
> want the loop to execute for) that will maintain its value between calls
to
> your ActiveX script. You do this by storing it as a Global Variable.

> Goto the Package Properties dialog and add a Global vaiable of type
Integer
> and set its initial value to 0.  Then your looping code should look
> something like this:

> option explicit
> Function Main
> Dim iLoop

>     iLoop = DTSGlobalVariables("LoopVar").Value
>     DTSDestination("OutputDate").Value = DateAdd("d", iLoop,
> DTSSource("SourceDate").Value)
>     if iLoop = 6 then
>         DTSGlobalVariables("LoopVar").Value = 0
>         Main = DTSTransformStat_OK
>     else
>         DTSGlobalVariables("LoopVar").Value = iLoop + 1
>         Main = DTSTransformStat_OK + DTSTransformStat_SkipFetch
>     end if
> end function

> Hope this helps,

> Neil


> >I am trying to generate multiple output rows from a single input row
using
> a
> >DTS ActiveX script.  The script accepts a single row (with columns for a
> >start and end date), then generates a row for each day within that range
to
> >a date dimension table.  Although I can do this with VB, I am hoping that
I
> >can simply us DTS.  I have a <<Do while  ... Loop>> that generates each
> >output row.  The loop ends with the statement <<  Main =
> >DTSTransformStat_SkipFetch  >>.  Main is the ActiveX function and I
expect
> >the DTSTransformStat_SkipFetch  status to cause the data pump to write
out
> >the current output and simply use the current input for the next
> >transformation.  The ActiveX script loops through as expected but writes
> out
> >only the last transformation.

> >Anyone knows what I am doing wrong?  I appreciate any and all help.

 
 
 

1. Multiple input rows into single DB record ?

Hi.
Our partner's mainframe writes a large file in which each row consists of two
field, TAG and DATA.
Multiple rows forms a set of data which describes one item (ie information of a
book). Each set of rows starts with tag '0001'. Number of tags for one set is not
known before but each individual tag-code are.

Sample of date may look like this
0001: 'first data of first set'
0002: data 1-2
0009: data 1-3
0234: data 1-4
0001: 'first data of second set'
0023: data 2-2
0023: data 2-3
0001: 'first data of third set'
... and so on

Now I'd like to read all data of each set into one row of a table in SQL Server
2k. The table has columns for all different tag-code.

Is there any way to do this by using dts and it's normal transformations?
I tried to look BOL and other sources but I have not found any hint.

Should I somehow read the data into (global?) variables first and when I notice
that new set has started then insert all variables into table. Note, that one tag
can be multiple times in on set, which means that I should concatenate these
datafields into single column.

Our partner say that they are not going to change dataformat, next step is XML
(which would be much easier to handle...).

I appreciate any help.

Regards,
Ben

2. Forms 6.0 userexits error!

3. Using DTS to update multiple tables from a single input row

4. Registry Writing

5. Converting 1:M rows into a single row with multiple columns

6. Can Oracle send variables to Word

7. stored procdure return a set of multiple rows or single row

8. SQL Puzzle

9. Creating Multiple Destination Rows from a single source Row

10. pulling multiple record rows into a single row

11. Splitting a single row into multiple rows

12. single row updates effect multiple rows....