1 person found this helpful
I'd be curious to see if you've come up with a solution yet, since it is an ongoing frustration for most CF/SQL Server set ups that DTS doesn't support the newer 2007 excel XLSX Zip format. I'm not sure how much help you are going to find on this particular forum, though, since it is a related topic, though not strictly coldfusion-related. I'm sure you've already checked on the popular SQL Server forums as well, but if not I would recommend sweeping those as well.
Is there any way you can generate your source files as the older Excel .xls format? That should fix your problem in terms of being able to import directly without any work-arounds.
No solution yet. Importing the file, with manual intervention, is easy. You can save manually save the file (SAVE AS) a lower version, and it imports fine. Or convert to a .CSV file. But we need the process to run "in production", 7 days/week, 365, around 4AM each morning, with no manual intervention. I'm sure a program can be written (JS, VB, etc.) to do the job, but my skills are not in that area. I'm a SQL, CF guy.
I appreciate the interest and advice. Much appreciated. I'll keep experimenting with this .DSN thing. I really don't understand why it even creates a .DSN file, which references the .XLSX file. We will be upgrading our SQL Server to the latest version later this year. I've got the only server in the company that hasn't been upgraded yet. It's also one of the busiest, with 2M lines of CF code, 19 major reports, and 20,000 hits/day by 5,000 employees.
That's probably why you don't see too many posts about this issue. Most people probably have the latest versions of SQL Server and Office already. Thanks again,
I cannot say I know much about it. But out of curiousity, are you able to read files if you set up a direct connection manually like mentioned here: