One of my clients has some very specific needs to get a spreadsheet run through some ETL processes I have created.
I have developed an SSIS .dtsx package which imports an Excel spreadsheet into SQL Server and then performs some transformation processes and then exports the results to another Excel spreadsheet. I am wanting to hand off the tool to the client so they can kick off the ETL process ad-hoc. In order to allow them to run the DTSX package, what are the minimum requirements that are needed to get this running on their system? Can this be done with SQL Express or do they need to have Integration Services running to execute the package?
Best Answer
You have delivered a solution that uses SSIS. SSIS requires an installation of SQL Server Standard, BI, or Enterprise Edition (depending on components used).
At this point, your client will need to spend ~ 8k per core (SE) to 23k per core (EE) for a license of SQL Server for their desktop. That's probably not what they're expecting.
What can you do?
Assuming they have SQL Server installed in their enterprise, it should be a matter of deploying to somewhere that instance can access and educating the client on where they need to copy the source spreadsheet to and where they can pick up the output. I'm fond of my ETL file processing pattern but go with whatever works best for you and your client.
That addresses getting the package so it can run with the right input. Now you need to figure out how the client can call it. I would create a SQL Agent job with no predefined schedule and then the world's tiniest application that simply calls
EXECUTE msdb.dbo.sp_start_job 'ExcelProcessor'That'll be an asynchronous process so you'd then want to do something like poll the output folder until a file appears or monitor the job table to see whether the job completes successfully before signalling the user that processing is complete.