How to Split Single Record into Multiple Records Using DTS?

There are many times a situation arises when we have to upload data from excel datasheet to SQL Server and the data in the sheet is not normalized. At that time, we need to make more than one record from a single record. Suppose you have a sheet in which each row in spreadsheet shows particular order and items in it. Now at the time of uploading data you want to split each order record into a separate record and have each item from that order belonging to separate row in the new transformation. In this example, for simplicity, I have source and destination as excel spreadsheet. Here is the ActiveX Script code.

Function Main()

    do while 1=1
        DTSGlobalVariables("gCounter") = CLng(DTSGlobalVariables("gCounter")) + 1
        select case CLng(DTSGlobalVariables("gCounter"))
        case 1
            DTSDestination("ID") = DTSSource("OrderID")
            DTSDestination("Item") = DTSSource("Item1")
        case 2
            DTSDestination("ID") = DTSSource("OrderID")
            DTSDestination("Item") = DTSSource("Item2")
        case 3
            DTSDestination("ID") = DTSSource("OrderID")
            DTSDestination("Item") = DTSSource("Item3")
        end select

        if DTSGlobalVariables("gCounter") < 3 then
            Main = DTSTransformStat_SkipFetch
            Exit do
        else
            DTSGlobalVariables("gCounter") = 0
            Main = DTSTransformStat_OK
            Exit do
        end if
    loop
End Function

I expect that you know simple transformation of data. Here what I did is created one ActiveX transformation which has 4 source columns and transformed into 2 destination columns. This program already knows that 1 row of spreadsheet needs to be split in 3 rows. One global variable “gCounter” is used which keeps track of how many records are splitted from a single record and if all the records are generated, fetch next record. Here above function goes in infinite loop as you can see in the while loop condition. Initially value of global variable is 0. When the first record is fetched, gCounter becomes 1. Now destination fields are populated with source record. Now function is exited with value “DTSTransformStat_SkipFetch”. This value prohibits the function from fetching next record while inserting the new record in destination with the values entered in destination fields. So our first record is inserted in the destination. Now source record pointer still points to the first record, but when the above function is called, it will get the second value from the source and inserts it in destination. Again the return value of function is “DTSTransformStat_SkipFetch”. This process repeats until we generated enough number of records to end the loop. When the loop ends, the return value would be “DTSTransformStat_OK”. This value will cause the program to fetch new record from the source. You may download source code by clicking on DTS Split Record Example. Please unzip the file in C:\ and open the DTS package DTS_Split_Record.dts from SQL Server Enterprise Manager.