A Simple Method to Load Excel Data

Question

Post reply

A Simple Method to Load Excel Data

Louis Bravo

SSC Veteran

Points: 249
More actions
January 15, 2024 at 12:00 am

#4355744

Comments posted to this topic are about the item A Simple Method to Load Excel Data
January 16, 2024 at 9:42 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

al-243208 SSC Rookie Points: 31 More actions · Answer 1

Sorry, what is the point to create a SSIS script AND C# code?

Isn't simpler to create just small C# program or even VBA script with the same functionality ?

demirelcam Newbie Points: 2 More actions · Answer 2

demirelcam

Newbie

Points: 2

January 15, 2024 at 8:34 am

#4361965

i am confused

Phil Parkin SSC Guru Points: 246988 More actions · Answer 3

al-243208 wrote:

Sorry, what is the point to create a SSIS script AND C# code?
Isn't simpler to create just small C# program or even VBA script with the same functionality ?

You're right, to a point. But if a company already has a suite of SSIS packages, it's tidier to 'keep everything in one place' and build another one. Having the code in SSIS also means that job scheduling and error-logging are built in.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Phil Parkin SSC Guru Points: 246988 More actions · Answer 4

Have you tested this script for performance with, say, one million rows in the Excel sheet? It would be interesting to know how it performs when compared with doing the import the 'standard' SSIS way.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Doctor Who 2 SSCrazy Eights Points: 8197 More actions · Answer 5

I was curious to know if your app was a WinForms app or a console app. This is because you have two lines of code (commented out) where you use MessageBox. So, I created a very simple console app using VS 2022, to test using MessageBox. Here's the whole app code:

namespace CanConsoleAppUseMessageBox
{
    internal class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("This is a console app");

            MessageBox.Show("Hello from the Console app");
        }
    }
}

The line with MessageBox won't allow the app to be built by VS 2022. Either it wants me to include System.Windows namespace (for WPF) or System.Windows.Forms namespace (for WinForms). But VS 2022 won't allow me to add either namespace. So, I conclude that either your app is a WinForms app or a WPF app.

Please correct me if I'm wrong.

This reply was modified 11 months, 2 weeks ago by Doctor Who 2. Reason: Added additional information

Rod

Phil Parkin SSC Guru Points: 246988 More actions · Answer 6

It's a piece of code which can be put into an SSIS Script Task and is therefore neither WinForms nor Console.

However, the MessageBox command does work when running the code locally during development and can be a useful debugging tool. This obviously does not work after the code is deployed to a server, however.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Doctor Who 2 SSCrazy Eights Points: 8197 More actions · Answer 7

Oh! I didn't know that this code would work within a SSIS Script Task. I've never used SSIS Script Task. Interesting. Well, I intend to share this with some people at work, who are looking for this solution.

Thank you!

Rod

Jeff Moden SSC Guru Points: 1004415 More actions · Answer 8

Phil Parkin will tell you that I'm not a big fan of SSIS or most of the any other 4 letter words in SQL Server.

That not withstanding and with the understanding that that I'm not a C# or VBA or other type of programmer, I think the premise of this article is great and sorely underplayed and under advertised. Here's a quote from the article...

Please note the only columns that will be read from the Excel sheet are the ones where the same column name exists in the SQL table. If the Excel column does not exist in the SQL table, it’s going to be skipped. If you need to read a new column from the Excel file, then all you must do is add the same column name to the SQL table.

That seems damned handy and my hat is off to the author, if it works and, sorry, no... I can't test it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

al-243208 SSC Rookie Points: 31 More actions · Answer 9

@Phil Parkin

> Have you tested this script for performance with, say, one million rows in the Excel sheet?

I just cannot imagine an Excel worksheet with one million rows. In our team experience Excel with a few hundred thousands crashes quite often at unpredictable times

In case of a few tens thousands rows SSIS will work faster than VBA. Not sure about C# program

Doctor Who 2 SSCrazy Eights Points: 8197 More actions · Answer 10

We're like you, @al-243208. We have Excel spreadsheets with hundreds of rows. Some into the thousands, but that's rare.

Rod

Louis Bravo SSC Veteran Points: 249 More actions · Answer 11

I already had the SSIS package loading Excel files to tables via data flow tasks components and doing many other tasks, but each time the users changed the Excel layout or renamed the tab or changed something else, the package started failing. The method that I implemented via the parameterized script resolved the issues with a simple few parameter updates or table updates without having to update and redeploy the package. This solution should work well for people who have similar issues in an existing SSIS package.

Louis Bravo SSC Veteran Points: 249 More actions · Answer 12

No, I didn't test with millions of Excel rows as my Excel files only contained 10-200 rows.

Phil Parkin SSC Guru Points: 246988 More actions · Answer 13

Louis Bravo wrote:

No, I didn't test with millions of Excel rows as my Excel files only contained 10-200 rows.

The point of testing against a very large dataset is to get an accurate idea of differences in performance. If SSIS 'standard method' imports 50,000 rows in ten seconds and this method takes ninety, that may be an important factor when considering which way to go. If all of your imports are just a few hundred rows, it probably makes little difference.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel