• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 967
  • Last Modified:

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.
0
scooby_56
Asked:
scooby_56
  • 5
  • 5
  • 3
  • +1
3 Solutions
 
alainbrydenCommented:
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.
0
 
scooby_56Author Commented:
This question is still not in SSIS zone. Can you please add to zones

SSIS_SSAS
and
DTS

0
 
scooby_56Author Commented:
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", "description to be configured here")
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





0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
CodeCruiserCommented:
0
 
alainbrydenCommented:
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 item

Open in new window

0
 
alainbrydenCommented:
Wait, you want all of them in one line, so you would need to concatenate the cmd string so you can execute it at the end:

    For Each item In Metrics
        '...assign sheetname in whatever way is appropriate (presumably all these sheets exist beforehand)
        cmdString = cmdString & "INSERT INTO [" & sheetname & "$] ([Name],[Value]) VALUES('" & item.Key & _
                    vbNewLine & "', 'test metric') GO"
    Next item

Open in new window

0
 
scooby_56Author Commented:
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"


0
 
CodeCruiserCommented:
In that case, here is a VB.NET code example

I am assuming that you have a command object configured and connected to Excel (if you need help with that, let us know)

I would suggest using a DataTable

Dim dTable As New DataTable
dTable.Columns.Add("Description", GetType(String))
dTable.Columns.Add("Date", GetType(Date))
'code to populate the table

For i As Integer = 0 to dTable.Rows.Count - 1
     dbcmd.CommandText = "Insert Into ... Values('" & dTable.Rows(i).Item(0) & ...
     dbcmd.ExecuteNonQuery()
Next
0
 
scooby_56Author Commented:
Thanks CodeCruiser: There was still a small way to go... namely, the OLE object syntax etc.

found some additional help with syntax from this great article
'http://support.microsoft.com/kb/306022/en-us

I just need to put loop round it now and use my collection/object values inside the loop iteration

- thanks for the help all, will close and distribute some assistance points


 
Dim connection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filePath & ";Extended Properties=EXCEL 8.0"
        Dim cn As New OleDb.OleDbConnection(connection)
        cn.Open()

        Dim cmd As New OleDb.OleDbCommand()
        cmd.Connection = cn
        cmd.CommandText = "INSERT INTO [Summary$] ([Name],[Value]) VALUES('desc', 'configure desc here')"
        cmd.ExecuteNonQuery()

        'add a second command to prove 2 executions will run...
        cmd.CommandText = "INSERT INTO [Summary$] ([Name],[Value]) VALUES('bb', 'configure bb here')"
        cmd.ExecuteNonQuery()

        cn.Close()

        'help from this article
        'http://support.microsoft.com/kb/306022/en-us

Open in new window

0
 
CodeCruiserCommented:
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?
0
 
NorieVBA ExpertCommented:
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.
0
 
scooby_56Author Commented:
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)
0
 
alainbrydenCommented:
Nope. Points should definitely be assigned on this one.
0
 
alainbrydenCommented:
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
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 5
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now