SSIS - Multiple inserts via Jet Database Engine
Hi,
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.
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.
alainbryden
Show us the code you use to successfully import one table and we'll show you how to convert it into a loop for multiple imports.
ASKER
This question is still not in SSIS zone. Can you please add to zones
SSIS_SSAS
and
DTS
SSIS_SSAS
and
DTS
ASKER
The destination Excel sheet was created like this....
CREATE TABLE `Summary` (
`Name` LongText,
`Value` LongText,
`Description` LongText
)
GO
A successfull SQL command string was built like this....
"INSERT INTO [" & SheetName & "$] ([Name],[Value]) VALUES('" & MetricName1 & "', 'test metric') GO"
My collection at present (can be changed however) looks like this
Dim Metrics As New Hashtable
Metrics.Add("Description",
Metrics.Add("Date", "date to be configured")
I need to either configure a loop container
OR
loop programatically to do the following pseudo code...
For each item in collection (e.g. hashtable)
Connect to a excel destination provider=MS Jet Engine etc.
Execute an SQL INSERT statement including item.name & item.value
End loop
CREATE TABLE `Summary` (
`Name` LongText,
`Value` LongText,
`Description` LongText
)
GO
A successfull SQL command string was built like this....
"INSERT INTO [" & SheetName & "$] ([Name],[Value]) VALUES('" & MetricName1 & "', 'test metric') GO"
My collection at present (can be changed however) looks like this
Dim Metrics As New Hashtable
Metrics.Add("Description",
Metrics.Add("Date", "date to be configured")
I need to either configure a loop container
OR
loop programatically to do the following pseudo code...
For each item in collection (e.g. hashtable)
Connect to a excel destination provider=MS Jet Engine etc.
Execute an SQL INSERT statement including item.name & item.value
End loop
Not sure if its useful but this is the looping example
http://www.databasejournal.com/features/mssql/article.php/3100621/T-SQL-Programming-Part-2---Building-a-T-SQL-Loop.htm
http://www.databasejournal.com/features/mssql/article.php/3100621/T-SQL-Programming-Part-2---Building-a-T-SQL-Loop.htm
VBA doesn't support native Hashtable functionality so I don't even know where you're getting that data type from. In therory, if you have a Hashtable that is a collection, then you should be able to loop using the following code:
Dim Metrics As New HashTable
Metrics.Add "Description", "description to be configured here"
Metrics.Add "Date", "date to be configured"
Dim item As Variant
Dim cmdString As String
Dim newSheet As Worksheet
For Each item In Metrics
'Add a new destination sheet for this item'
Set newSheet = Worksheets.Add()
'... perform other actions'
cmdString = "INSERT INTO [" & newSheet.Name & "$] ([Name],[Value]) VALUES('" & item.Key & "', 'test metric') GO"
'... perform other actions'
Next itemASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
alainbryden thanks for the assistance here:
I understand that the Scripting Language for the 'Script Task' is VB .NET as opposed to VBA.
Importing 'System.Collections' namespace gives me access to the Hashtable here - it appears in the intelli sense....
I'm actually looking for code to actually execute the SQL command within the loop as well as buildin the SQL command string. CAn you help me here?
CodeCruiser: Thanks to you too... the link you gave shows how to loop in T-SQL. As above - im looking to execute the SQL command inside a VB. Net loop iteration via the "MS Jet Datbase Engine"
I understand that the Scripting Language for the 'Script Task' is VB .NET as opposed to VBA.
Importing 'System.Collections' namespace gives me access to the Hashtable here - it appears in the intelli sense....
I'm actually looking for code to actually execute the SQL command within the loop as well as buildin the SQL command string. CAn you help me here?
CodeCruiser: Thanks to you too... the link you gave shows how to loop in T-SQL. As above - im looking to execute the SQL command inside a VB. Net loop iteration via the "MS Jet Datbase Engine"
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
After these lines
Dim cmd As New OleDb.OleDbCommand()
cmd.Connection = cn
you can add the loop I showed you. Just change dbcmd to cmd. Where is the data coming from?
Dim cmd As New OleDb.OleDbCommand()
cmd.Connection = cn
you can add the loop I showed you. Just change dbcmd to cmd. Where is the data coming from?
I know this is closed, but can I ask where the data you are inserting is coming from?
Is it any format which is accessible and structured so that you could query it?
If it is you might be able to use INSERT...SELECT rather than INSERT ...VALUES.
So you wouldn't need a loop.
You could also create a mega SQL statement.
INSERT INTO [Sheet1$] VALUES (x1, y1, x2, y2,..., xn, yn)
I've done this sort of thing the 'other' way, ie from Excel to SQL Server and it works, I assume it would work the other way.
Apologies if I'm way off base.
Is it any format which is accessible and structured so that you could query it?
If it is you might be able to use INSERT...SELECT rather than INSERT ...VALUES.
So you wouldn't need a loop.
You could also create a mega SQL statement.
INSERT INTO [Sheet1$] VALUES (x1, y1, x2, y2,..., xn, yn)
I've done this sort of thing the 'other' way, ie from Excel to SQL Server and it works, I assume it would work the other way.
Apologies if I'm way off base.
ASKER
imnorie - thx for your contribution.
The data is actually being generated on the fly within a script task.
The values are report-name, date, time-range (that the report covers), description and such. Possibly could create a Table collection first but would think i would need to 'INSERT' into this first. So... after putting the loop in, the solution i have above gets around the limitation of one SQL statement per script task nicely - and my data is where it should be. :o)
The data is actually being generated on the fly within a script task.
The values are report-name, date, time-range (that the report covers), description and such. Possibly could create a Table collection first but would think i would need to 'INSERT' into this first. So... after putting the loop in, the solution i have above gets around the limitation of one SQL statement per script task nicely - and my data is where it should be. :o)
Nope. Points should definitely be assigned on this one.
Vee Mod,
I did read the actual closing comments before Objecting.
The email notification did not contain any indication that a point split was underway the way one normally does when such is the case. It was the automated message normally received when a question is being closed with no points assigned.
Perhaps it would be suitable to investigate why the system gave the notification it did if the destined action was in fact a points split. Let me know if a thread is started.
Alain
I did read the actual closing comments before Objecting.
The email notification did not contain any indication that a point split was underway the way one normally does when such is the case. It was the automated message normally received when a question is being closed with no points assigned.
Perhaps it would be suitable to investigate why the system gave the notification it did if the destined action was in fact a points split. Let me know if a thread is started.
Alain