Using ANY free SQL Server version to simply create a local SSIS/dtsx file run on local machine

Question

Yes I KNOW there are a lot of questions about this on styack overflow, and I'm sorry but all I found was reading through dozens and dozens where every one seemed to disagree, or things were terribly version specific and more importantly - many of them were out of date as the rules seem to change over time.

All I want to know is, can I download and install (actually on my client's computer), any free or almost-free version of SQL Server (I really don't care if it's 2005, 2008, 2012 etc) to include enough functionality like BIDS / SSIS so that I could create an SSIS package that would only need to do the following, which I would write: 1. A script task (in vb.net) to do some file/folder work 2. A flat file connector and a data flow task to import some data into the local instance, after first truncating some tables probably 3. Output a text file from a query.

Also, in order to do that, assume this client has ZERO relevant things installed. Will I need to do a long and tedious installation of many things separately - like sql server, bids, visual studio, etc? If so, any comments to guide me thru that jungle would be helpful and appreciated.

Answer 1

TL;DR;

"can I download and install...any free or almost-free version of SQL Server" ... that has SSIS.

No

Deets

SQL Server Express Edition contains the Import Export wizard which allows for the creation of an SSIS package that performs EL (extract and load) but no T(ransformation). Strike 1 for meeting your requirements (of having SSIS).

Strike 2 is that the package cannot be saved so your client would be forced to run through the same mouse clicks per processing cycle.

Developer Edition is for development purposes. You would be an unprofessional partner for you clients if you advocated anything less than full compliance with the MS license agreement. They might love you saving them a great deal of money on licensing...until the lawyers come after them. Then it's going to cost them a lot more than having been straight to begin with.

From the 2014 developer license, the final sentence of 2A is clear as crystal

2. INSTALLATION AND USE RIGHTS.

a. General. One user may install and use copies of the software to design, develop, test and demonstrate your programs. You may not use the software on a device or server in a production environment.

b. Demonstration. Any person that has access to your internal network may install and use copies of the software to demonstrate use of your programs with the software. Those copies may not be used for any other purpose.

c. User Testing. Your end users may access the software to perform acceptance tests on your programs.

What is free

SQL Server Express - you can use Express to store up to 10GB of data per instance. This is licensed for production use and it's free. If you install SQL Server Express with Advanced Services, you get access to reporting services, text engine and other capabilities.

.NET framework - since you have the chops to write "a script task (in vb.net) to do some file/folder work", just keep writing to include the ability to read/write files and connect to a database.

Answer 2

If you want my tuppence worth!

Express 2012 and upwards no longer requires registration for redistribution. SQL Express can be included as part of your application installation.

The following link is a recent MSDN forum thread on the subject, which also carries links to embedding in an application.

MSDN Forum Express Redist

SSDT is the replacement for BIDS (as from SQL Server 2012). This is also available as a free download.

SQL Express does not allow you to create an ISC in the instance, nor does it include SQL Agent. That does not stop you from running DTSX packages "manually" i.e. thru SSDT or by using DTExec. This execution could be parceled up in your application.

Some elements of SSIS are also not available with Express, for example the "Fuzzy Logic" transforms.

You may need to build a test deployment of your app; sql express & package and test it on a separate machine to ensure that DTexec will work on a target machine, and to definitively determine what you need to include.

The MSDN forums may also be of value.