I have a cvs file with a header, detail, and a footer with totals.

I' like to insert the header information into a table, the detail into
seperate table, and the footer total into another seperate table.

I've tried using Transform Data Task to insert the detail into a
table. Then using the lookup tab to insert the header and footer
information into another table.

May problem is I can't seem to get the data to insert into the header
and footer tables but I can get the detail data to insert into the
table without a problem.

I'm not even sure if this is the best way of inserting the data.


Here's my Transform Data Task ActiveX script.

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

' Copy each source column to the destination column
Function Main()
If DTSSource("Col001") = "HEADER" then
DTSLookups("HeaderCount").Execute DTSSource("Col002")
else
If DTSSource("Col001") = "FOOTER" then
DTSLookUps("FooterTotal").Execute DTSSource("Col002")
else
DTSDestination("CompanyCD") = DTSSource("Col001")
DTSDestination("CustomerCD") = DTSSource("Col002")
DTSDestination("CalendarYear") = DTSSource("Col003")
DTSDestination("CalendarMonth") = DTSSource("Col004")
DTSDestination("SoldToPostalCD") = DTSSource("Col005")
DTSDestination("ShipToPostalCD") = DTSSource("Col006")
DTSDestination("SalesDollars") = DTSSource("Col007")
DTSDestination("StandardCost") = DTSSource("Col008")
DTSDestination("Units") = DTSSource("Col009")
end if
end if
Main = DTSTransformStat_OK
End Function


For the lookup tab

Insert for the Header:

INSERT INTO tblHeaderCount(HeaderCount)
VALUES (?)

Insert for the Footer:

INSERT INTO tblFooterSalesTotals(SalesDollars)
VALUES (?)

Any help would be greatly appreciated.
Mary

You can use a three step bulk insert sql statement to accomplish this by
making use of the first_row parameter, you may need to do some parameter
driven work to get the last row # for the third step.

"mary bird" <mary.bird (AT) la-z-boy (DOT) com> wrote