Import data from multiple Excel files using SSISSSIS Tutorials In this interesting post, we will learn how to import data from multiple Excel files to a single destination say, SQL Server using SSIS. This question is asked in many MSBIinterviews and also by many followers, so the wait is over. Let me explain the scenario to understand this problem. Suppose, there is one folder and on a daily basis data is kept in separate Excel files in this folder. Now, you are asked to extract the data from this folder containing multiple Excel files to the SQL Server. In simple words, we have to extract data from a folder containing new Excel file being added each day. NOTE   An important point to ponder here is that the column structure format of all the Excel files remains the same. There can be numerous solutions to solve this problem of Import data from multiple Excel files using SSIS. Figure%201%20-%20Timesaving%20Tricks.jpg' alt='Excel Drop Down List Files Folder' title='Excel Drop Down List Files Folder' />I have about 50 or so Excel workbooks that I need to pull data from. I need to take data from specific cells, specific worksheets and compile into one dataset. We will use For. Each loop container approach to implement this scenario. In the below article, we will be solving this practically by taking an example. STEP 1  Folder with multiple Excel files. Similar Programs Like Virtual Dj. Create a folder and place multiple Excel files in this folder. But, make sure that all Excel files have same columns format identical schema. NOTE  Sheet name should be uniform across all the Excel files present in the folder. Folder containing multiple Excel files same Schema One for each day. STEP 2  SSIS Package. Create one SSIS package and give it a desired name, say Import data from multiple Excel files. Open SQL Server Data Tool SSDT and Create new SSIS project. STEP 3  Add SSIS Variable. Right click in the control flow pane and choose Variables. Add a variable at package level with name as File. Name and Data type as String. Add SSIS variable. STEP 4  SSIS For. Each loop container. In the Control Flow pane, drag and drop For. Each loop container from the toolbox to the surface. WebpartlarArasindaBaglantiFoto_14.png' alt='Excel Drop Down List Files Folder' title='Excel Drop Down List Files Folder' />SSIS For. Canada Grains Council 1999 Statistical Handbook Of Tamil more. Each loop container. Double click on the For. Each loop component to edit it or Right click on the Foreach loop container and select Edit option. Click on Collection tab to specify Folder path, Files type and option to retrieve file name. Now, provide the folder path where you have created it to keep the multiple Excel files. File type will be. Excel files. You can take a reference from the below screenshot. NOTE   . xls means all file names with an xls extension. As, you have multiple excel files present in a folder so will fetch all Excel files present in the folder specified above in the Folder path. Configure SSIS Foreach loop container. Go to Variable Mapping tab and map the created SSIS variable in Step 3. Finally, click OK button to finish with For. Each loop configuration. For. Each loop containers Variable Mapping tab. STEP 5  SSIS Data Flow Task. Inside For. Each loop container, drag and drop Data Flow Task DFT. You can give DFT a desired name, say Import data from multiple Excel files. Adding Data Flow Task into For. Each loop container. Thousands of virtually daily articles and discussions of all aspects of Microsoft Excel. This blog is run by Microsoft Excel MVP, Dick Kusleika. Double click on Data Flow Task to configure it with Source, Transformations and Destination. In Data Flow pane, drag and drop Excel source from the toolbox. STEP 5A  Excel source configuration in SSISDouble click on the Excel source to get the Excel source window editor. Click on New button and then browseto the location of first Excel file i. C Php. RingDay1. Excel Source configuration in SSISNow, choose Sheet. Name of the Excel sheet. This finishes the configuration of Excel source in SSIS. Excel source editor Connection Manager tab. Columnstab Choose the columns from the Available source. Excel source editor Columns Mapping tab. STEP 5B SSIS OLEDB destination. Drag and drop OLEDB destination component to the Data flow pane. Now, connect Excel source to OLEDB destination. Connect Excel source to OLE DB destination. Double click on OLEDB destination to configure. Now, map it to your database and create a new table or use existing table. You can take a reference from the below screenshot attached. OLEDB destination editor Connection Manager tab. Mappingtab Map Input columns to Destination columns. OLEDB destination editor Column Mappings tab. STEP 6  Update SSIS Excel source configuration. NOTE   This is the main step here  to import data from multiple Excel files. So, put your concentration on this point to understand the tricky part. Right click on Excel connection manager you can find Excel connection manager in the Connection Managers pane and select Properties. Other ways to go to properties are Select the component and then press f. Alt and Enter, etc. In the properties pane for Excel connection manager, find Expressionsand expand it. This will pop up Property Expressions editor. Now, Choose Excel. File. Path property and assign the value for expression as User File. Name. This will provide dynamic connection for each Excel file with the help of variable User File. Name used in For. Each loop container. Click on Evaluate expression button at the bottom left to check for errors. Follow the below image for a clear understanding of these steps. Expression for SSIS Excel Connection Manager. Once you set the Expression for Excel connection manager, you will get a red cross sign on Excel source component. The reason for this error is that the source component is not aware about the connection because it will dynamically accept the connection User File. Name when package will get executed. Setting Excel file path Expression for SSIS Excel Connection Manager. STEP 7  Set Data Flow Task Delay validation property TRUELastly, a very crucial point to import data from multiple Excel files using SSIS is to set Delay validationproperty of the Data Flow Task TRUE. Setting Delay validation TRUE for Data flow task in SSISSTEP 7  Execute SSIS package. Finally, lets Executeour SSIS package. Execute SSIS package Import data from multiple Excel files. Data in SQL Server beforeexecuting SSIS package. Data in SQL Server table before executing SSIS packageImport data from multiple Excel file SSIS package execution Control Flow and Data Flow pane. SSIS package execution Control flow and Data flow pane. You can now viewyour imported data from multiple Excel files into SQL Server table. Final SQL Server table after SSIS package execution Import data from multiple Excel files. I hope you must have enjoyed our post on How to import data from multiple Excel files to SQL Server using SSIS. This is a very important question asked during MSBI interviews. With this, we reached to an end of this post. I understand sometimes it gets difficult working with Excel source. So, try these steps very carefully and still if you have any queries or issues then do commentbelow. We will really appreciate your feedback. VN F 1. 9. 2. 21. Rating 9. 01. 0 1. VN F 1. 9. 2. 21. Rating 8 from 8 votesImport data from multiple Excel files using SSIS, 9.