Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 Export to Excel Issue with code

Author  Topic 

dhumphries
Starting Member

18 Posts

Posted - 2008-12-30 : 13:03:23
I am exporting to Excel in vb.net using Visual Studios for Applicaiton. I think I am on to somehting here but I am rather baffled. I have run the follwoing code in debug and it runs just fine there are no errors. however it does not update the cell in the spreadsheet. any advise as to what I might have done worng.

Dim AccessConnection As New System.Data.OleDb.OleDbConnection

Dim _conn As String

Dim SchemaTable As DataTable

Dim _filename As String = "C:\Documents and Settings\dhumphries\Desktop\test.xls"

_conn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & _filename & ";" & "Extended Properties=Excel 8.0;"

Dim _connection As OleDbConnection = New OleDbConnection(_conn)

Dim _command As OleDbCommand = New OleDbCommand

_command.Connection = _connection
Try

_command.CommandText = "Update [city$c4:c4] set f1 ='DAN'"

_connection.Open()
_command.ExecuteNonQuery()

'MessageBox.Show("The Data Imported Successfully!")

Catch e1 As Exception

'MessageBox.Show("Error!.Your Excel Spread Sheet has some problems.")

End Try
Dts.TaskResult = Dts.Results.Success
End Sub

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 13:12:45
to export single, isnt it enough to use OLEDB source task with your query and excel destination task pointing to your excel file inside a data flow task?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 13:13:31
or even use OPENROWSET rather than SSIS if its for an adhoc need.
Go to Top of Page

dhumphries
Starting Member

18 Posts

Posted - 2008-12-30 : 14:07:25
This will need to be a runable job in the end which is why I would like it to be is ssis. I can not use an excel destination becuase I need to be able to update a specific cell in an exsiting file. for example write the total from the query to cell h7. the excell destination I do not beleive allows you to specify a cell value like that.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 14:10:12
then i think its better to use openrowset and call the query from your sql job.
Go to Top of Page

dhumphries
Starting Member

18 Posts

Posted - 2008-12-30 : 15:36:13
OK I see what you are saying but the Ad Hoc Distributed Queries is not turned on. I have tried to get this turned on but becuase this is another deparments server I am unlikely to get that permission. So is it possible to do this using a VSA script. It seems like that would work but I am not exactly sure what the code would look like.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 03:27:12
http://www.eggheadcafe.com/software/aspnet/30605392/vbscript--excel-read-fil.aspx
Go to Top of Page

dhumphries
Starting Member

18 Posts

Posted - 2009-01-05 : 09:14:44
For anyone who is interested here is how I finally resolved this issue.

The first step in the control flow is an SQL Task. I run my Select sum statement and store the sum of the value in a variable.
I then use a script component and use the dts varialbe setup in the previous step to update my excel spread sheet. The Script uses the System.Data.OleDb.OleDbConnection method to update the Excel sheet. If you are using a similiar mersion of Visual studios for SQL server as I am you may have tried to use the ExcelObj library only to find you have no reference for it. in fact you have no COM Tab at all. so I am using this method. The biggest thing you want to know about this method is that the script will be reading your excel file as if it were reading an SQL Table. so that means you need to have a header row and you need to have some sort of unique identifier in each row. I just made two hidden values. The first hidden value is the header I called i and a. pretty generic but simply id and amount. the second hidden value is the i column itself. It is what allows me to call the write cell in the where statement. Attached is the code and hopefully you may find this helpful if you are new at SSIS As I am.

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Data.OleDb
Imports System.Data.SqlClient


Public Class ScriptMain

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()
'
' Add your code here
Try
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim myCommand As New System.Data.OleDb.OleDbCommand
Dim sql As String
Dim SVNG8Amt As String = CType(Dts.Variables("User::SVNG8Amt").Value, String)
If SVNG8Amt = "" Then
SVNG8Amt = "0"
End If
MyConnection = New System.Data.OleDb.OleDbConnection _
("provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + _
"'C:\Documents and Settings\dhumphries\Desktop\test.xls';Extended Properties=Excel 8.0")

MyConnection.Open()
myCommand.Connection = MyConnection
sql = "Update [CWLP$] set a = " & SVNG8Amt & " where i='5h'"
myCommand.CommandText = sql
myCommand.ExecuteNonQuery()
MyConnection.Close()


Catch ex As Exception
MsgBox(ex.ToString)
End Try
MsgBox("Updated ")

Dts.TaskResult = Dts.Results.Success
End Sub

End Class
Go to Top of Page

Rashmi_P
Starting Member

1 Post

Posted - 2012-07-27 : 17:33:11
Hi,

I tried the solution mentioned by you but it didn't work for me. I am getting error

Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.OleDb.OleDbException: External table is not in the expected format.

Did you ever come across this error while working on the example code?

Please reply.

Rashmi.


quote:
Originally posted by dhumphries

For anyone who is interested here is how I finally resolved this issue.

The first step in the control flow is an SQL Task. I run my Select sum statement and store the sum of the value in a variable.
I then use a script component and use the dts varialbe setup in the previous step to update my excel spread sheet. The Script uses the System.Data.OleDb.OleDbConnection method to update the Excel sheet. If you are using a similiar mersion of Visual studios for SQL server as I am you may have tried to use the ExcelObj library only to find you have no reference for it. in fact you have no COM Tab at all. so I am using this method. The biggest thing you want to know about this method is that the script will be reading your excel file as if it were reading an SQL Table. so that means you need to have a header row and you need to have some sort of unique identifier in each row. I just made two hidden values. The first hidden value is the header I called i and a. pretty generic but simply id and amount. the second hidden value is the i column itself. It is what allows me to call the write cell in the where statement. Attached is the code and hopefully you may find this helpful if you are new at SSIS As I am.

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Data.OleDb
Imports System.Data.SqlClient


Public Class ScriptMain

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()
'
' Add your code here
Try
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim myCommand As New System.Data.OleDb.OleDbCommand
Dim sql As String
Dim SVNG8Amt As String = CType(Dts.Variables("User::SVNG8Amt").Value, String)
If SVNG8Amt = "" Then
SVNG8Amt = "0"
End If
MyConnection = New System.Data.OleDb.OleDbConnection _
("provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + _
"'C:\Documents and Settings\dhumphries\Desktop\test.xls';Extended Properties=Excel 8.0")

MyConnection.Open()
myCommand.Connection = MyConnection
sql = "Update [CWLP$] set a = " & SVNG8Amt & " where i='5h'"
myCommand.CommandText = sql
myCommand.ExecuteNonQuery()
MyConnection.Close()


Catch ex As Exception
MsgBox(ex.ToString)
End Try
MsgBox("Updated ")

Dts.TaskResult = Dts.Results.Success
End Sub

End Class


Go to Top of Page
   

- Advertisement -