We have an interesting scenario here at work which to be honest doesnt seem very difficult but we have never had the opportunity to try this out.
One of my co-workers has written a dts package containing one simple transformation. This dts package simply reads from a file and puts it into a table. The interesting project requirement is that each record contains up to 23 number of row inserts. Is there any way to do this insert reading one row of data at a time? Basically, reading contents of line 1 requires 23 row inserts depending on data given. Right now, we read a row and insert one row.
Thanks for any help, it is greatly appreciated.
--
Mr. Antonio G. Curcio
MCAD

Tony,

From your narrative, I assume the 23 inserts are to the same table. Then use
an ActiveX transformation. Create a global variable that you can use as a
counter and increment it after each insert. You keep to the same row by using
SkipFetch:
something like:
Function Main()
If DTSGlobalVariables("Counter").Value <= 23
'do insert
DTSDestination("Colxyz") = DTSSource("Colabc")
....
DTSGlobalVariables("Counter").Value = DTSGlobalVariables("Counter").Value
+ 1
Main = DTSTransformStat_SkipFetch or DTSTransformStat_OK 'this line is
key to keeping same row
else
DTSGlobalVariables("Counter").Value = 1 'reset counter for next row
Main = DTSTransformStat_SkipRow 'leave this row as you're done with it
end if
End Function


Hope this helps.

Charles Kangai, MCT, MCDBA
Author of Learning Tree's 4-day course: "SQL Server 2005 Integration
Services" http://www.learningtree.com/courses/134.htm
Author of Learning Tree's 4-day course: "SQL Server Reporting Services"
http://www.learningtree.com/courses/523.htm
email alias: charles
email domain: kangai.demon.co.uk


"Tony" wrote:

Thanks Charles, that worked well.
"Charles Kangai" <CharlesKangai (AT) discussions (DOT) microsoft.com> wrote