A Simple Method to Load Excel Data

  • Comments posted to this topic are about the item A Simple Method to Load Excel Data

  • 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 ?

  • i am confused

  • 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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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 5 months ago by  Doctor Who 2. Reason: Added additional information

    Rod

  • 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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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

  • 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)

  • @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

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

    Rod

  • 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.

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

  • This was removed by the editor as SPAM

  • 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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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

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