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

Hadi · Answer 1 · 2019-02-13T19:10:31.587

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

edited Feb 13 '19 at 19:10

answered Feb 13 '19 at 00:01

Hadi

36,233
13
65
124

The code in the third link for vb does not look right for bulk copy. It is trying to do a reader.Read in a loop and passing the reader. Not even sure what that would do but nothing good. – Mary Feb 13 '19 at 04:26
@Mary i don't prefer this method at all. I am with SSIS approach. But iam trying to givr some insight and showing possible solutions – Hadi Feb 13 '19 at 07:15
Hadi - I should probably mention a little more of the complexities surrounding the task. I have to run this process in a loop on an unknown number of Excel files located on a network. Thus, I'm using the script task because I need to be able to dynamically change the excel file path as it processes this unknown number of workbooks at any given moment. I have thought about setting up a temp table to gather the data from each excel file using the bulk copy method and then simply run the update via SQL command to the SQL Server table, but I don't like this method because it involves extra steps. – Justin CR Feb 13 '19 at 16:05
I would like to directly connect to the excel file and run the UPDATE query directly to the SQL Server table without having to load a temp table in SQL server first and run the update query off of that. I saw someone had gotten this to work using C# in SSIS at this link but I can't seem to replicate it using VB: https://stackoverflow.com/questions/22797917/import-excel-xslx-file-into-sql-server-difficulty-in-updating-table – Justin CR Feb 13 '19 at 16:06
I should also mention that we have to use SSIS because my company doesn't allow us to enable Ad Hoc Distributed queries which is required for using OPENROWSET. – Justin CR Feb 13 '19 at 16:08
@JustinCR i think your approach should work. I will update my answer in a while – Hadi Feb 13 '19 at 16:34
@JustinCR have you declared table type in sql server for the datatable. So you can pass it as parameter – Hadi Feb 13 '19 at 16:35
1

@Hadi - Thanks so much for your help. Do you know how I would declare the TypeName using VB code in this case? I think this could be the missing piece. I will also try setting up the User-Defined TableType object in SQL Server first and then passing it in as a parameter in the script task, as mentioned in your link above. – Justin CR Feb 14 '19 at 02:06
1

@JustinCR using an SQLCommand and executeNonQuery method. Create a type as mentioned in the link i provided where you should describe the datatable structure. – Hadi Feb 14 '19 at 04:35
1

@JustinCR also note that the error message you are receiving is a general exception thrown in script task. To read the real message. Add a `try... Catch` block and use the Dts.FireError method inside the catch block to rethrow the exception. – Hadi Feb 14 '19 at 04:38
@JustinCR at the end. As a side note. You have to read the [Tour page](https://www.stackoverflow.com/tour) to learn more about asking, upvoting and accepting answers and to get informed badge – Hadi Feb 14 '19 at 04:42

score 1 · Accepted Answer · answered Feb 14 '19 at 20:25

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!

i think you should accept or up vote the other answer since it solved the issue. And you can leave your answer to provide more details. But it seems that the issue is solved based on the **Update** section from Hadi answer — Yahfoufi, Feb 15 '19 at 09:58
Hadi's answer was very helpful in that it led me to utilize the stored table type and procedure as the starting point. However, it was missing the additional VB code required to execute it through the script task, which was the original method of task execution. Thus, I think my answer is the complete answer in this context. — Justin CR, Feb 15 '19 at 15:56
@JustinCR you have the right to accept your own answer. Happy for solving the issue. You can just upvote an answer if it is helpful an accept your own. Good luck — Hadi, Feb 15 '19 at 19:16
@JustinCR for this reason you should accept your answer and upvote the other to mention that you have the complete version but the other is helpful. — Yahfoufi, Feb 18 '19 at 10:41
I up voted Hadi's answer since it was very helpful and accepted mine as the final answer. Please let me know if there is anything else that I need to do. Thanks! — Justin CR, Feb 19 '19 at 15:28

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

2 Answers2

Update 1

Initial Answer