SSIS - Multiple inserts via Jet Database Engine
Posted on 2010-11-23
Im trying to peform multiple inserts into an Excel destination in SSIS 2005.
i tried to create a string for the SQL command containing multiple insert commands followed by GO. however discovered that the MS Jet Databse Engine only supports one SQL command per script task.
I can acheive this if i add multiple script tasks per insert command however there are 10 plus rows so would like to do this in some kind of loop. Either loop container or via script.
I'm setting up a number of variables programatically via a script task. The variables can be stored independantly or in some kind of collection/Object.
So far Ive set up a Hashtable via script task, then placed a foreach loop container with a sql task inside to execute the SQL Command - but im having trouble configuring the forloop container to extract th values from the hash table and make them avaiable to the SQL command.
Is there a better way to do this or can anyone help with the config of the container?
Or a script example to iterate through some kind of collection and execute SQL to an excel destination.