SSIS - Getting Funky Excel Export into Table Format for Upload to SSMS

Question

SSIS - Getting Funky Excel Export into Table Format for Upload to SSMS

phaenggi

SSC Enthusiast

Points: 147
More actions
March 23, 2018 at 8:50 am

#344507
Hello All,
I've been tasked with taking the following three attached Excel files converting them into a table format and uploading it to an SSMS table. Files are dropped into a location daily morning. The problem is the data contained within the Excel files is a mess in general and we have no other way to export this data from the particular ERP.
A few of the issues are that the columns can change in number and in names. The files also show grand totals and subtotals. Lastly the first and 2nd column can have blanks.
I figured I might be able to use a script component with unpivot here, but nothing really seems to work. I could potentially run some VBA on the files to correct them, but would rather just use SSIS if I can. Please have a look at the three files to see what I'm speaking to in terms of the messy data. Any suggestions on how I might make this work using SSIS? It just seems that no matter what I do I would have issues with the metadata constantly being out of sync..........
Thanks!
BB

Chrissy321 SSCoach Points: 15763 More actions · Answer 1

I sympathize with your situation. I have no technical suggestions because as you have suggested with the data changing there are no reliable solutions to the problem. Here are some thoughts to consider.

Revisit the exports out of the source system and try to get an actual file useful in data processing
Make this an operational not a development task. Maybe someone has to input this into a data entry screen. Alternatively maybe someone needs to normalize the file before passing it on to data processing
As developers we should strive to build automation with very low failure rates. Explain to your manager that anything built to do this will have very high failure rates
If you as the developer are forced to provide operational support for tasks like this then at one point you will not have time to develop since you will be constantly fixing things.

Good luck!

phaenggi SSC Enthusiast Points: 147 More actions · Answer 2

Chrissy321 - Friday, March 23, 2018 2:21 PM
I sympathize with your situation. I have no technical suggestions because as you have suggested with the data changing there are no reliable solutions to the problem. Here are some thoughts to consider.
Revisit the exports out of the source system and try to get an actual file useful in data processing
Make this an operational not a development task. Maybe someone has to input this into a data entry screen. Alternatively maybe someone needs to normalize the file before passing it on to data processing
As developers we should strive to build automation with very low failure rates. Explain to your manager that anything built to do this will have very high failure rates
If you as the developer are forced to provide operational support for tasks like this then at one point you will not have time to develop since you will be constantly fixing things.
Good luck!

Hi Chrissy,

Thanks for the tips! I figured I'd post the issue here just in case I might be missing something. I just don't think there is a way to automate this and will explore some of your suggestions.