Data Driven Query Task

forkhead · June 20th, 2006, 04:14 PM

Hi All -- I have a unique situation

I want to execute a stored procedure for data pulled from a flat file, which I have figured out.

My question is this: I have a flat file where the data must insert several rows in the same table; the data occurs 3 times on the flat file in succession:

CONTRACT01 DATA FOR ROW 1 DATA FOR ROW 2 DATA FOR ROW 3

And it needs to be represented in a table like this:
ContID ContData
CONTRACT01 DATA FOR ROW 1
CONTRACT01 DATA FOR ROW 2
CONTRACT01 DATA FOR ROW 3

It does get a little bit more complex-- I can't do this through a simple insert; I want to use a stored procedure (for a lot of reasons; error handling, and putting data in several tables at once without having to do lots of lookups).

I have it figured out how to "loop" through this data using ActiveX Script with simple Inserts but that won't suit my needs.

Can someone enlighten me how to execute a stored procedure passing the proper segmented part of my record as a parameter to my stored procedure?

Thank you!

"A spirit with a vision is a dream with a mission"

forkhead · June 22nd, 2006, 11:01 AM

I figured it out. I'm using a Data-Driven Query task and this is the script:
'************************************************* *********************
' Visual Basic Transformation Script
'************************************************* ***********************

' Copy each source column to the destination column
Function Main()

    DTSGlobalVariables("counter") = DTSGlobalVariables("counter") + 1

    Dim strTID
    Dim strTempVal

    strTID = DTSLookups("IDLookup").Execute (DTSSource("Col001"))

    Select Case DTSGlobalVariables("counter")
        Case 1
            DTSDestination("TST2_VALUE") = DTSSource("Col002")
            DTSDestination("TST_ID") = strTID
            strTempVal = DTSTransformstat_SkipFetch + DTSTransformstat_InsertQuery
        Case 2
            DTSDestination("TST2_VALUE") = DTSSource("Col003")
            DTSDestination("TST_ID") = strTID
            strTempVal = DTSTransformstat_SkipFetch + DTSTransformstat_InsertQuery
        Case 3
            DTSDestination("TST2_VALUE") = DTSSource("Col004")
            DTSDestination("TST_ID") = strTID
            strTempVal = DTSTransformstat_SkipFetch + DTSTransformstat_InsertQuery
        Case Else

    End Select

    If DTSGlobalVariables("counter") >= 4 Then
        strTempVal = DTSTransformStat_SkipRow
        DTSGlobalVariables("counter") = 0
    End If

    Main = strTempVal

End Function

'Adding the DTSTransformstat_SkipFetch & DTSTransformstat_InsertQuery did the trick.

"A spirit with a vision is a dream with a mission"