[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 712
  • Last Modified:

SQLBulkCopy in VB.net

I am making a change to a current SSIS package.  I need to use a VBScript Task to import an excel spreadsheet into a table with the same column names.  I am new to VB.net scripting and I want to see if there is an example I can see that uses SQLBulkCopy to import the Excel Spreadsheet.
0
seasonmmclane
Asked:
seasonmmclane
  • 4
  • 2
1 Solution
 
HoggZillaCommented:
Why can't you use a Data Flow task to import the xls to a table? I ask because there may be other options than going to a script.
0
 
seasonmmclaneAuthor Commented:
That was the original method.  For various reasons, they are not wanting to use that method anymore.  
0
 
HoggZillaCommented:
Bulk Copy will be done using an Execute SQL Task, no need to use a script. I have never used Bulk Copy to get data from a table to an .xls "table". Have you done a proof of concept in SQL?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
seasonmmclaneAuthor Commented:
I want to import the excel spreadsheet into a table in SQL Server.  The table and the spreadsheet have the same columns.
0
 
seasonmmclaneAuthor Commented:
I have some code that seems like it should work.  However, whenever I run that script task component to test it, I get the following error: "the script files failed to load."  Here is my code:

Imports System.Data.OleDb
Imports System.Data.OleDb.OleDbConnection
Imports System.Data.Common
Imports System.Data.SqlClient
Imports System.IO
Public Class ScriptMain
    Private Sub ImportExcel(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Dim excelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\myworkbook.xls;Extended Properties=""Excel 8.0;HDR=YES;"""
        Using connection As New OleDbConnection(excelConnectionString)
            Dim command As New OleDbCommand("Select * FROM [mysheet$]", connection)
            connection.Open()
            Using dr As DbDataReader = command.ExecuteReader()
                Dim sqlConnectionString As String = "Data Source=myservername;Initial Catalog=mydb;User ID=myid;Password=mypassword"
                Using bulkCopy As New SqlBulkCopy(sqlConnectionString)
                    bulkCopy.DestinationTableName = "mytable"
                    bulkCopy.WriteToServer(dr)
                End Using
                dr.Close()
            End Using
        End Using
        MsgBox("Data Inserted Successfully", MsgBoxStyle.Information)
    End Sub
End Class

I cannot even debug this code to figure out if there is a line that is causing the issue as the code errors immediately since the code won't load.  On this component I have the PrecompileScriptIntoBinaryCode option set to TRUE.  Any ideas???
0
 
seasonmmclaneAuthor Commented:
I was able to correct this problem by changing the line above that reads:

Private Sub ImportExcel(ByVal sender As System.Object, ByVal e As System.EventArgs)
to read:

Private Sub Main()
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now