Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SSIS - Multiple inserts via Jet Database Engine

Posted on 2010-11-23
20
Medium Priority
?
958 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 3
  • +1
20 Comments
 
LVL 20

Expert Comment

by:alainbryden
ID: 34196335
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
ID: 34196759
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
ID: 34196804
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 83

Expert Comment

by:CodeCruiser
ID: 34204180
0
 
LVL 20

Expert Comment

by:alainbryden
ID: 34205404
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 1000 total points
ID: 34205435
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
ID: 34207084
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
 
LVL 83

Assisted Solution

by:CodeCruiser
CodeCruiser earned 1000 total points
ID: 34207118
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
ID: 34207565
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
ID: 34208081
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 34

Expert Comment

by:Norie
ID: 34208135
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
ID: 34234598
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
ID: 34239740
Nope. Points should definitely be assigned on this one.
0
 
LVL 20

Expert Comment

by:alainbryden
ID: 34240101
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

Python: Series & Data Frames With Pandas

Learn the basics of Python’s pandas library of series & data frames and how we can use these tools for data manipulation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

705 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