Having issues importing Excel data into existing SQL Server table using SSIS script task (VB)

Question

I have successfully loaded the datatable object with data from the table in the Excel file. How do I use this data table object in an UPDATE query to update an existing SQL Server table?

I encounter this error:

Exception has been thrown by the target of an invocation.

at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

Public Sub Main()

    Dim fileToTest As String
    Dim SheetName As String
    Dim connectionString As String
    Dim excelConnection As OleDbConnection
    Dim excelCommand As OleDbCommand
    Dim ODA As OleDbDataAdapter
    Dim dtExcel As New DataTable()
    Dim SQLConn As SqlClient.SqlConnection
    Dim SQLCmd As SqlClient.SqlCommand
    Dim SQLPara As SqlClient.SqlParameter

    'open a connection to the excel file
    fileToTest = "C:\Users\testuser\Documents\test\mytestfile.xls"
    connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
    fileToTest & ";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"""
    excelConnection = New OleDbConnection(connectionString)
    excelConnection.Open()

    'open a SQL connection to the LRPSF_Source_DB SQL Server DB
    connectionString = "Data Source=mysqlserver.net\sqlentdb1d;Trusted_Connection=True;DATABASE=LRPSF_Source_DB;CONNECTION RESET=FALSE"
    SQLConn = New SqlClient.SqlConnection(connectionString)
    SQLConn.Open()

    'fetch the data from TEST table in Excel file using a command query and store in datatable object
    SheetName = "TEST$"
    excelCommand = excelConnection.CreateCommand()
    excelCommand.CommandText = "SELECT * FROM [" & SheetName & "]"
    excelCommand.CommandType = CommandType.Text
    ODA = New OleDbDataAdapter(excelCommand)
    ODA.Fill(dtExcel) '<- this datatable object is filled with the data successfully

    'using the dtExcel datatable as a table input, update the existing dbo.TEST_INPUT_SIMPLE SQL Server table
    SQLCmd = SQLConn.CreateCommand()
    SQLCmd.CommandText = "UPDATE TIS SET TIS.MY_COLUMN = TISX.MY_COLUMN " &
                         "FROM dbo.TEST_INPUT_SIMPLE TIS INNER JOIN @source AS TISX " &
                         "ON TIS.UPDATE_ID = TISX.UPDATE_ID"
    SQLCmd.CommandType = CommandType.Text
    SQLCmd.Parameters.AddWithValue("@source", dtExcel).SqlDbType = SqlDbType.Structured
    SQLCmd.ExecuteNonQuery() '<-- the program errors on this line

    Dts.TaskResult = ScriptResults.Success

End Sub

Answer 1

Update 1

I think the problem is that you didn't have declared a table structure in SQL Server. And instead of that you are using SqlDbType.Structured without specifying the type name:

SQLCmd.Parameters.AddWithValue("@source", dtExcel).SqlDbType = SqlDbType.Structured

For more information on how to pass a datatable as parameter to SQL Server check the following SO question:

• How to insert a data table into SQL Server database table?

In addition, in the link you have mentioned in your comments they used dbo.tStudent as type name:

param.SqlDbType = SqlDbType.Structured;
param.TypeName = "dbo.tStudent";

Initial Answer

If you are looking to update an SQL Table by joining an Excel file, this is not the right way. You pass a table as parameter in this way. There are many approach to join Excel table with SQL Table:

In SQL Server

• Import Excel to a staging table in SQL Server and then run the query
• Add Excel worksheet as a Linked Server in SQL Server
• Using OPENROWSET in SQL Server

In SSIS

• In SSIS, use an Excel Source and an OLEDB Command in the DataFlow Task, the update command will be written in the OLEDB Command

In VB / C#

• Import data to SQL then run update query

I didn't provide too much details in this answer, but i tried to give some insights on the approaches that you can use better than a script task to achieve your goal. Each link contains the information that you need and much more

Answer 2

I solved this issue by performing the following steps:

1) Create a User-Defined TableType in the database:

CREATE TYPE [dbo].[MyTableType] AS TABLE(
    [UPDATE_ID] NVARCHAR(255),
    [MY_COLUMN] NVARCHAR(255)
)

2) Create a stored procedure containing previously created tabletype and update query:

CREATE procedure UpdateDB
    @myTableType MyTableType readonly
AS
BEGIN
    UPDATE TIS SET TIS.MY_COLUMN = TISX.MY_COLUMN 
    FROM dbo.TEST_INPUT_SIMPLE TIS INNER JOIN @myTableType AS TISX 
    ON TIS.UPDATE_ID = TISX.UPDATE_ID
END

3) Using the VB script task, fetch the Excel data and run the stored procedure:

Public Sub Main()

    Dim fileToTest As String
    Dim SheetName As String
    Dim connectionString As String
    Dim excelConnection As OleDbConnection
    Dim excelCommand As OleDbCommand
    Dim ODA As OleDbDataAdapter
    Dim dtExcel As New DataTable()
    Dim SQLConn As SqlClient.SqlConnection
    Dim SQLCmd As SqlClient.SqlCommand

    'open a connection to the excel file'
    fileToTest = "C:\Users\testuser\Documents\test\mytestfile.xls"
    connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
    fileToTest & ";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"""
    excelConnection = New OleDbConnection(connectionString)
    excelConnection.Open()

    'open a SQL connection to the LRPSF_Source_DB SQL Server DB'
    connectionString = "Data Source=mysqlserver.net\sqlentdb1d;Trusted_Connection=True;DATABASE=LRPSF_Source_DB;CONNECTION RESET=FALSE"
    SQLConn = New SqlClient.SqlConnection(connectionString)
    SQLConn.Open()

    'fetch the data from TEST table in Excel file using a command query and store in datatable object'
    SheetName = "TEST$"
    excelCommand = excelConnection.CreateCommand()
    excelCommand.CommandText = "SELECT * FROM [" & SheetName & "]"
    excelCommand.CommandType = CommandType.Text
    ODA = New OleDbDataAdapter(excelCommand)
    ODA.Fill(dtExcel) 'object is filled with Excel data'

    'load the dtExcel object into @myTableType object and run the stored procedure'
    SQLCmd = SQLConn.CreateCommand() 
    SQLCmd.CommandText = "[dbo].[UpdateDB]"
    SQLCmd.CommandType = CommandType.StoredProcedure
    SQLCmd.Parameters.AddWithValue("@myTableType", dtExcel) 
    SQLCmd.ExecuteNonQuery() 'run the stored procedure containing the update query'

    Dts.TaskResult = ScriptResults.Success

End Sub

Thanks for your help!