Tutorial – How to Load Multiple Excel Files into SQL Server via SSIS
Articles,  Blog

Tutorial – How to Load Multiple Excel Files into SQL Server via SSIS


Okay so in This Video I’m going to show you how to load multiple excel Files Into A sequel Server Database So the first thing, We want to do is Check The Excel File and Check the Columns and Mimic This Into A table so We got name Title Company Location So, we’re Going to go Into Here And this is Just a staging table the Data is Obviously not Clean but that’s for Another Video so go Ahead and Create A table This Test Table So Test Table And There’s our test table here Okay, Call our Columns in Here Now Let’s Hop, over to Video Studio let’s create a new Project Going to be an Integration services Project okay so the first thing, We want to do in Here Is Add in The We Can Add in The excel file Connection so a new Connection Excel Add Let’s go Ahead and find where our Files are Located mines are on my Desktop Here There We go So, we’re Going to Load in These Files so Let’s go Ahead and Click ok that’s our excel Connection Manager let’s AdD in our Destination Yeah new See in Here Going to be very Test Database Just it Okay, now, we’re Ready to build some stuff so let’s go over to our tool Box Grab in a Data Flow task Go in Double Clicks Open Up the, Data Flow Task I’m going to Grab the excel source I Can find it Here So for now, we’re Just going to use the regular excel source pick the sheet Make Sure has the Data Inside Columns That’s fine Those are the ones We want? We’re Going to Grab a Data Conversion because These are Unicode in Excel and We want non Unicode? So, we want a Data Conversion Go and Connected I want all these Like to Rename Them to Whatever I’m converting it to so i’m going to be Converting this string Now Normally I don’t Make them this Long for the length but I haven’t Used A derive Column to split them up yet something’s Going to leave my 255 Now Let’s Just go Ahead and put in The Destination or Destinations Make Sure the to our Table Mappings, We’re Going to go ahead and Remap all These because, We don’t want? The Unicode Ones, We want the Non Unicode? Okay, let’s then go, Into A quick save And we’re Ready to run it so let’s run Just This one for Now and let’s it start So it Looks Like it went Through And Let’s go Ahead and Check over Here Yep They all Came Through Now One Thing some People might get an Error With that I forgot to Mention is you want to go ahead and Go Back to your the Properties of the Package Itself The Entire The Entire Project go to the Properties go to Configuration Debugging and You want to hit this to false Because a lot of Times They’ll get an Error When using excel Files if the runtime Is set to true so That Worked out Now Let’s Load multiple Files So for that we’re Going to need to put in some Variables so let’s go Ahead and go back in Here And let’s go to our variables Window So I’ve Been using 2010 this is my first Time using 2015 So There is about Variables are Going to be here Let’s go Ahead and Dock it down Here So, we’re Going to create a couple new Variables so the first one Is going to be for the file Path Changes To String And so that we’re Going to put the Path of the entire File Including The file Name Next, we’re Going to do the final Folder which Is just what all the other Files are Next One for file Name So this One Roof Is going to use the very first File And Lastly an archive Folder Where we’re Going to toss the files Once we’re Done Loading them Which is also your I believe yeah? You’d Seen Here I got a bunch of archive Folders run Outside and Delete this just so we Can know Okay, Now the Variables Are Set up let’s go Ahead and create A Breech Loop so I’ll go back to your toolbox go back to the Control Flow Because the first loop, is over There We’re Going to Grab the for each Loop Container We’re Going to toss this Guy in Here and Real Quick Let’s go to the excel Connection Manager, we’re Going to Change Up The Expression Here to the file Path In Here so excel file Path and in our Variables, We have our file Path? Evaluate Okay, that’s so, we Can Make it Dynamic? And Now Let’s go Ahead and go Into our for you loop Properties Right under Selection and Then in Here, We’re Going to go Ahead and Add in So in Here, We’re Going to go to Variable mappings real Quick and Just pick the file Path leave it to 0 in the Collection Area We’re Going to go to The Expressions and We’re Going to Add in the File Folder Here Under Directory Click on the Variable This Is our file Folder that’s Where all the files Are Held, over here, we’re Going to, also pick Fully-Qualified We want Just Two xls Files so, we’re Going to Change that to xls? and over Here We’re Going to click on Browse and go Ahead and locate Exactly where the files are Or? If You want to Make it Easy and it was Just Copy and paste from the Folder Either way it Works and That Should Be all Set up so, let’s go Ahead and run this Now and See if We get The Files loaded, Let’s go Ahead and Truncate This Table See Right Now We Have 1192 Records so it Should be Different if This Works So it Says all done Let’s go Ahead and See All right so it looks like, We got Thirteen seven Six seven all Different Letters so, We know it Worked, We Can Just real Quick order by Just to see that We got all the letters in Here and There You go that’s how you load in Multiple Excel Files Into SmS

26 Comments

  • Manikanta Mokidi

    Error: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80004005 "Unspecified error". This occurs when the ForEach Enumerator cannot enumerate.
    can you resolve this error sir pls

  • Max S.

    Hi msk2982, firstly, thanks a lot! I've got to import data from an Excel file into a DB on a recurrent basis, I've found a few methods, but so far this one is the neatest! Secondly, I noticed that you created an Archive_Folder variable, however, in your end result, your spreadsheets don't appear to be copied there. After importing the data from the spreadsheet, I would need to transfer it to the archive folder and remove it the original folder. Would you have any source that you can refer me to and that would explain how to get this done? Thanks!

  • Andrés Castrillo

    I followed the steps. I could load the first file but when I tried to load it using "For each" I got this error [SSIS.Pipeline] Error: "Excel Source" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

    If I load the files one by one it works just fine. But I have more than 60 files. Life is too short to do it one by one

  • Moises Bueso

    been trying this method but won't work with the format .xlsx (Excell 2016), been googling but isn't clear, any advice!!

  • Rupak Rai

    i am facing minor problem in 4:08 time. i am not getting any option in name of excel sheet name.there is no file can you plese help me.

  • Razvan Zota

    Hey Mohamed Khan, thanks for the video, i do encounter one issue: One of the column cannot be converted from unicode to non-unicode string data types. Also please share the link to the video where you changed the package to 32 bits since i also want to run it with .xlsx format. Thanks a lot

  • Anitha Msb

    Hi. Thanks for the video. Could you please help with data conversion for dates, alphanumeric and numbers (integers and decimals).

  • L L

    Not sure why it keeps giving me an error, saying Opening a rowset for "xxxxxx" failed. Check that object exists in the database.
    I'm not sure why, I changed all delay validations to true. Still no luck. Any suggestions?

  • Jonatán Fekete

    SOLVED! Hi! Thanks for the tutorial! I could recreate the same for my files with no problem and also the archiving part. First run was successful with loading & archiving. But when I tried to run it for the second time with other files, it didn't run as it was looking for the file that is specified in the File_Path variable. I need this solution to run scheduled and load all *.xlsx files from a folder. Can I make SSIS not to look for the exact file specified in the File_Path variable at each run? Thanks a lot in advance!

  • Jose Fernando Vg

    Great! I had a hard time with the 32-64bit version thing. Just found out how to jump over that and this video worked perfectly to upload the Excel files into a SQL table. Thanks!

  • ligozambo zambo

    Thanks for this.

    I encountered a problem,
    when I add new data to the excel file, and generate it again in sql, the old rows are repeated.
    Can you help me with this?
    Thanks.

  • joshi7405

    I am currently working on IPBC for Colaberry and I am stuck on an SSIS assignment
    Do you mind helping me out? I have watched your video times and I am getting an error.

  • Henry Effiom

    Hello
    Pls I have a problem which needs a solution

    I want to know how to load multiple excel sheets as tables in SSIS
    In such a way that the sheets in an excel file becomes SQL table

    Examples an excel file named Customer with sub sheets with names credentials and products
    They will be loaded to SQL Server as different tables with names dbo.credentials and dbo.products

    A video would be well appreciated
    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *