Can I run SSIS packages with SQL Server 2008 Express/Web or Workgroup

Question

I have looked at the SQL Server 2008 feature comparison matrix and it lists the express/web and workgroup editions as having the SSIS runtime. Does this mean it is possible to develop SSIS packages using the developer edition, and then deploy and run them on a server running one of the lowly SQL Server editions such as SQL Server 2008 Express edition?

Answer 1

The workgroup edition only has primitive import and export capabilities. As far as I can confirm, if you want to create SSIS packages using any data transformations you need to get SQL standard edition or higher.

Answer 2

You need dtexec to run SSIS packages from command line.

In SQL2005 Express you got dtexec and necessary tools with

• SQL Server 2005 Express Edition with Advanced Services
• Microsoft SQL Server 2005 Express Edition Toolkit

However, without SQL Agent you have to use other scheduling methods.

More information: http://daron.yondem.com/default.aspx?month=2006-05

Caveat: I haven't actually tried this and there are reports that dtexec just fails with a licence error. In my case, I just needed to run a dtsx once and did it with Developer edition with SQLExpress as data flow destination.

Answer 3

OK, here's the story. You can install DTEXEC using SQL Server 2005 Express Edition with Advanced Services or the Toolkit as above. However, this version of DTEXEC is not fully functional. If one of your data flow tasks tries to do something that isn't allowed you will get a message like this:

   Description: The product level is insufficient for component "<component>" (1828).

This means that the particular component uses something which isn't supported in the installed version of DTEXEC. I have noticed, for example, that this will happen if you use a DataReader source, which it appears I have to do to import from ODBC.