If your team stores similar Excel files in a shared folder, like sales reports, inventory logs, or project trackers, chances are you're stuck manually combining them. That’s fine for one file… but what about 50?
Let’s fix that step by step, or Watch the video here
With in conjunction with a Foreach Loop Task in SSIS, you can load dozens (or hundreds) of Excel files directly into SQL Server, automatically, with no code.
Create the Package
Here’s how it works:
1.Place all Excel files in one folder
These files should have the same structure (e.g., same columns like “Date,” “Amount,” “Region”).
2. Set up a Foreach Loop Task to loop through a Foreach File Enumerator
-
- Point it to the folder containing your Excel files. (We set a variable called sFileFolder with the folder path.)
- Set a filter to find all .xlsx files in that directory. (We set a variable called sFilesFilter with the filter *.xlsx.)
Capture the full path of each file in a variable. (We set a variable called sFQFileName to store each of the fully qualified file names that the Foreach Loop enumerates.)
Add Foreach Loop:
Name: Foreach Loop Container - Foreach Excel File
Collection: Foreach File
Add the Expressions in the Collection editor to set the values of the Directory and FileSpec properties of the Foreach Loop with the variables sFilesFolder and sFileSpec we just created.
You can see the Folder and Files Filter populated by the expressions.
Complete the Variable Mappings. Map the variable sFQFileName to index 0 which will hold each Fully qualified retrieved file name returned by the Foreach Loop within each iteration of the enumeration.
At this point you have created a package that iterates excel files in a folder with no code, creating three variables, providing them values and configuring a Foreach Loop. Now what? For each of those files let’s load them to SQL. Next we will create a Data Flow Task that will run inside the Foreach Loop for each excel file iterated in the enumeration.
3. Inside the Foreach Loop:
-
- Add a Data Flow Task. You can use the basic Data Flow Task for this example, however for more complex cases provides an arsenal of options to simplify the development that is not available out of the box from Microsoft.
- Inside the Data Flow Task, add to the data flow.
-
- Configure the with a new connection to one of the excel files so we have a temporary file to map the columns from. We will set an expression to dynamically substitute the file name from the sFQFileName for each excel file iterated from the Foreach Loop.
-
- Select the worksheet name.
-
- Verify the column setup.
-
- Go to the properties of the Excel Connection Manager we have just created and look for the Expressions. Click the 3 dots to create the Property Expression for ExcelFilePath where we will set the value to our sFQFileName variable. This will dynamically provide the current Excel File fully qualified name to the Excel Connection to be used in the iteration of the data flow.
-
- Add an OLE DB Destination to the data flow so we can write the data to your central SQL Server table.
-
- Create the OLE DB Connection to be used by the OLE DB Destination.
-
- While creating the new OLE DB Connection, you can create a new table using the following SQL create statement.
CREATE TABLE [dbo].[_demo_Load_Similar_Excel_Files] (
[Date] nvarchar(250),
[Amount] nvarchar(250),
[Region] nvarchar(250)
)
You can now see the table in your SQL Server.
The OLD DB Connection is set to use this SQL table as the destination.
- Click on the Mappings and you can see the input columns from the automatically maps the columns to our Destination columns is the SQL table represented in the OLE DB Destination.
-
- Now you should be able to switch back to the Control Flow tab and run the package and see that it executes.
-
- You can now query your SQL table and see that the data from all three excel files that were in the folder has now been loaded into your one central SQL table.
-
- You can now schedule this package to run each week or on the schedule that is agreed upon within your organization.
4. Optional: Move processed files
After each file is loaded, use to archive or rename the file.
See the full demo video:
Why this is a big deal:
You’ve just automated the consolidation of an entire department’s data entry workflow. What used to take hours can now run on a schedule, hands-free.
Take your development even further with
For example, in this article, we are using files where column names are consistently named and column names in the source files match the column names in the destination. In more advanced scenarios, if your column names are inconsistent from file to file, or your source columns do not match the destination column names, you can use the Dynamic Data Flow capabilities in to do dynamic column mapping at runtime from meta data stored outside your SSIS package. This makes maintaining packages easier and helps the developers make changes without having to open the SSIS package to make changes and avoids having to redeploy the package. This saves the developer time, the company money and users receive quicker change delivery.
Let’s say Johnny in US decides to change the column name from “Amount” to “Dollars” and adds the file to the folder where the SSIS package will process the files. Then Johnny wonders why his file didn’t get loaded. Our current package will not be able to handle the file because now the mapping is no longer valid. This happens often when your departments deliver the files. The way things are currently set up, the developer will have to open the SSIS package in their development environment, make the change and then redeploy. Your developers spend a lot of time making changes to existing packages because of column name changes. However, if you use the Dynamic Data Flow capabilities in , the developer will only have to make a column mapping change in a mapping table that lives outside the package. The developer will not have to open the package in their development environment to make the change. They just change the mapping stored within a table. That’s the power of COZYROC. Saving your developers’ time and your company money and delivering data to your end users allowing them to make business decisions faster while the data still matters.
With the power of COZYROC’s extensive suite of advanced SSIS components, you now have become the new hero in your data initiatives.
See all of .
COZYROC SSIS+ components suite is Free for download and development within Visual Studio.