I’m currently having an issue with an SSIS package I built. Said package is an ETL which extracts data from a .xlsx file in a server and loads it to a table in my database. There are hundreds of files in this server path, each with a unique file name. My SSIS package, extracts the data from each file, and dynamically adds two additional columns to the table in the database. One column for the file name, and the other for a date which is parsed from that file name. After running into quite a few issues with my data source, I was able to create a variable for the excel data source, and the package executed without any errors. The additional columns were added, and the correct data was populated into these columns. However, once I went over the data to ensure everything was how it needed to be, I discovered a ton of duplicates. Turns out, what my foreach loop is doing, is going through each and every one of the files in the server path, extracting the file name from each one, but the rest of the data getting populated into the table, is from just one of the files. In other words, it’s only looping through different files to get the file name and date, but all of the other columns are getting ignored and are instead getting populated with data from the first file on the list. I’ve been working on this package for over a week now and cannot seem to pinpoint what is causing this issue. I’m guessing it has something to do with my source variable, but I’ve tried changing it and nothing fixes it. Any insight on this would be greatly appreciated.
Thanks in advance!