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
