SQL Server to Excel Import and Export Tool

This article explains the rationale for creating a new tool for exporting and importing data between SQL Server and Excel to save you having to attempt such a task using the SQL Integration Services (SSIS) tool.

First, here is a link to the tools which are provided free along with full source code in the hope they prove to be useful additions to your toolset:

https://github.com/robinwilson16/ExcelSQLImporter

https://github.com/robinwilson16/ExcelSQLExporter

?

I think we have all been there where we are asked to integrate the latest cloud system or dashboard the organisation has just purchased and the marketing people at the software company sort of implied it would all just work in a kind of plug and play mindset. However once IT install it, the team who purchased it then realise that it is plug with no play and once the contract is signed, the marketing people are not quite as easy to get hold of and the organisation is left to work out the play side themselves to get it to integrate with other systems and processes and may have agreed to a very short timescale to achieve this.

As a developer you would sort of expect that exporting a Microsoft Excel file from a Microsoft SQL Server using Microsoft Visual Studio and Microsoft Integration Services would be fairly simple and led into a false sense of security, mainly by the fact they all contain the word Microsoft so should work well together. You would be forgiven for thinking these tools would work seamlessly and perhaps also be plug and play but, aside from the simplest data structures, this is rarely the case.

Speaking to other developers it turns out they all feel the same (e.g. https://stackoverflow.com/questions/79214163/ssis-export-data-containing-long-15k-field-values-to-excel) and try to avoid SQL Server Integration Services wherever possible. They resort to what would seem like, on the face of it, more complicated approaches such as PowerShell scripts or just setting a reminder each day and saving it manually, until they want a holiday. If you can run your query in SSMS and then copy and paste to Excel, format datetime values which for some reason always show as all zeros then upload via WinSCP and you are done, then surely a Microsoft tool designed to automate data transfer tasks between Microsoft products should be simple.

I mostly work with colleges and a recent task involved exporting the list of courses for a newly merged college group to their new website by saving it to an Excel file and uploading it to the FTP each day which should hardly be a challenging task. However, due to the fact the course marketing data came from a variety of sources, some of it contained a lot of hidden HTML code that inflated the number of characters held in a certain table cell up to a max of around 15k characters which described the course and learning outcomes. For some reason known only to Microsoft, the SSIS tool decides the size of this column is going to be 256 characters.

I first start by Googling the error and remembering the “Advanced” editor thinking oh yes that must be the answer given it is more advanced than the standard editor (a bit like the turbo button on a slow computer). I will just change the data type in there to be an NVARCHAR(MAX) as surely that will fit. This setting appears in at least four places for some reason. After changing them all you then think you have got around this weird behaviour and can now export your file but then are dismayed to see that you still get the same error and the “Advanced” editor has changed all your values back for you as it somehow thinks you are just being greedy by your request for such a large cell.

Next you then realise there is a way to disable this almost Clippy? like unhelpful behaviour so your NVARCHAR(MAX) value will remain in place and you think you have solved this and the “Advanced” editor may now save the values you just specified believing you that you did intend to provide such large values.

However, it still doesn’t work and you realise that even though you are exporting data to an Excel cell that can, according to Microsoft, hold 32,767 characters, the SSIS tool has decided your spreadsheet is different to all the rest and this cell can only hold under 1% of this.

At this point you are on your second coffee and all your past memories of previous SSIS escapades are coming back to you and you remember your trick of moving the largest row to be line 2 of the spreadsheet as opposed to perhaps line 20 and suddenly the SSIS tool then sees this and the problem goes away so you think, ah ha, I fooled it into working correctly.

You are so relieved to see a green tick that you don’t initially notice that each time you run the task it is appending data to the end of your Excel file and if your objective is to provide a full export of refreshed data this is far from ideal. You think you will just run a task to empty the file but then find there is not one so must delete and re-create the sheet so are ready to go with this solution. However as soon as you delete and recreate the sheet the SSIS tool re-evaluates your sheet and again decides it will only ever hold 256 characters and forgot it had just seen a 15k value in there.

Whilst you sit there staring into your empty coffee cup you then think oh well, I will just leave one dummy row in the file with a super long value which involves keeping a copy of the file to replace with the previously populated one and appending to that one.

Now you start to contemplate how to remove this single dummy row at the other end in the college WordPress website and write a second task, and also remember that even though Microsoft says all its services are designed with security and best practice in mind, the SSIS tool only supports unencrypted FTP servers and less secure email servers and often forgets your passwords and it is at this point that you decide to give up and go down another avenue such as developing your own tool which you may find you can write much faster and may well be much more robust when the SSIS tool can “forget” your data types and lengths and grind the process to a halt again, that is if you do ever get it working.

At least now you know if there is a bug/issue in your code you will be able to fix it, and it does not rely on some partly antiquated, partly mystical process as is the case with the SSIS tool.

So, this is my methodology for creating the tool which will hopefully resonate with other developers out there who work with Microsoft data tools and hopefully this tool will save you some pain and time.