0

Looking for some help please. I am trying to build an SSIS package that will download a file from a web-portal every hour and will load it in to SQL table. Now, i have a script task in my package that will download the file. Initially i downloaded the file as .xls. When i try to open the file initially, it comes up with an error message that file format doesn't match and data in the file might be unsafe or corrupted. However, if i manually open it and then save it as .xls or xlsx, it works perfectly fine but because this whole process needs doing every hour so i don't want to go that manual route. When i downloaded the file as Excel xml, it opens perfectly fine. I have attached the .xml file template. Now, is there a way, this .xml file can be re-saved automatically, that i can load dynmically. Or how to load Excel XML files directly without converting in to any other format. I have tried using SSIS XML Source editor but it didn't work for some reason. enter image description hereenter image description here

user3482527
  • 227
  • 1
  • 2
  • 22
  • Try using a file system task in ssis. Use the 'rename file' option to change the .xls file to an .xlsx then try your import. What happened when you tried the XML load? – Aaron Dietz Feb 22 '18 at 15:16
  • Hi Aaron, great idea, will give it a try. I will let you know the outcome. Many thanks Mate. – user3482527 Feb 22 '18 at 15:18
  • Why not just process the xls? that is SSIS preferred Excel file type. Processing xlsx requires an additional download. – KeithL Feb 22 '18 at 15:48
  • @AaronDietz Unfortunately, file system task didn't work. I have tried all possible combinations (e.g. downloading it as .xls and then renaming it as .xls or .xlsx or .csv but none of them worked. It throws an initial error message that file format is not supported and content might be corrupt but when i manually save it as same extension or any other extensions, it works perfectly well – user3482527 Feb 22 '18 at 16:02
  • Bummer - what was your issue with the XML import? Here is a guide that looks decent, it's a bit much to outline here: https://www.mssqltips.com/sqlservertip/3141/importing-xml-documents-using-sql-server-integration-services/ – Aaron Dietz Feb 22 '18 at 16:04
  • @KeithL He keith, good to see you back mate :). I have checked quite a few forums, looks like there is a known issue with .xls and .xlsx files when downloaded from web links, Files open up with a warning/error message (it might be something to do with company's security policy to block external content). But re-saving them manually fixes the issue. So ssis package can't connect to initially downloaded file and this package needs running every hour, i don't to follow manual route of saving the files again. So looking for a permanent/dynamic fix. – user3482527 Feb 22 '18 at 16:06
  • Does your source open in notepad? If so can you post a sample set. – KeithL Feb 23 '18 at 16:15
  • That's how i resolved the issue. Please use below link. https://stackoverflow.com/questions/49046681/how-to-load-an-xml-spreadsheet-2003-xml-in-to-sql-server-using-ssis-file-down – user3482527 Apr 13 '18 at 10:55
  • That's how i eventually resolved the issue. https://stackoverflow.com/questions/49046681/how-to-load-an-xml-spreadsheet-2003-xml-in-to-sql-server-using-ssis-file-down – user3482527 Apr 13 '18 at 10:56

0 Answers0