Solved

SSIS - Multiple inserts via Jet Database Engine

Posted on 2010-11-23
20
921 Views
Last Modified: 2012-05-10
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
Comment
Question by:scooby_56
  • 5
  • 5
  • 3
  • +1
20 Comments
 
LVL 20

Expert Comment

by:alainbryden
Comment Utility
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
 
LVL 12

Author Comment

by:scooby_56
Comment Utility
This question is still not in SSIS zone. Can you please add to zones

SSIS_SSAS
and
DTS

0
 
LVL 12

Author Comment

by:scooby_56
Comment Utility
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
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
0
 
LVL 20

Expert Comment

by:alainbryden
Comment Utility
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
 
LVL 20

Accepted Solution

by:
alainbryden earned 250 total points
Comment Utility
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
 
LVL 12

Author Comment

by:scooby_56
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 83

Assisted Solution

by:CodeCruiser
CodeCruiser earned 250 total points
Comment Utility
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
 
LVL 12

Assisted Solution

by:scooby_56
scooby_56 earned 0 total points
Comment Utility
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
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
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
 
LVL 33

Expert Comment

by:Norie
Comment Utility
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
 
LVL 12

Author Comment

by:scooby_56
Comment Utility
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
 
LVL 20

Expert Comment

by:alainbryden
Comment Utility
Nope. Points should definitely be assigned on this one.
0
 
LVL 20

Expert Comment

by:alainbryden
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now