can I use SSDT/BIDS/SSIS using SQL Server 2012 express?

Question

Ideally, I want to make SSDT/SSIS packages on my desktop PC that I can test and deploy to a production server environment. On my PC I have: Windows 7 Visual Studio 2008 Visual Studio 2010 (shell) SQL Server Express 2012 (local dev copy of prod) [supposedly] BIDS (Business Intelligence Development Studio) and SSDT (SQL SErver Data Tools) etc.

On my production server I have: Windows Server 2008 SQL Server 2012 Visual Studio 2010 (shell)

I cannot load/run SSIS (SQL Server Integration Services) onto my machine because SQL Server express doesn't allow for it, as suggested by these articles:

http://support.microsoft.com/Default.aspx?id=963070

http://msdn.microsoft.com/en-us/library/cc645993.aspx#SSIS

And I am getting this error when trying to look at a "package.dtsx" file in VS:

Microsoft Visual Studio is unable to load this document: To design Integration Services packages in SQL Server Data Tools (SSDT). SSDT has to be installed by one of these editions of SQL Server 2012: Standard. Enterprise. Developer, or Evaluation. To install SSDT, run SQL Server Setup and select SQL Server Data Tools.

When I run the SQL2012Express installer, it says SSDT is installed already.

Question #1: But why can't I just point a local VS2010/SSDT/SQL2012Express at my production SQL2012 environment? (Microsoft is getting their money legitimately!)

Question #2: I can get it to work on the production server, but isn't it bad form to develop on a production server? (and it complicates source control etc.)

Solution 1

You must have One of those versions installed. The SQL server express edition with advanced tools only includes reporting. Not ssis. Sql serverv dev edition is actually pretty cheap.

Solution 2

To be able to create SSIS packages you'd want the following:

1) For SQLServer 2008 SSIS packages - BIDS, either integrated into VS2010 or as a stand-alone. If you also want SQL server, then I'd use the dev edition of SQLServer that is an optional install with VS2010.

2) For SQLServer 2012 SSIS packages - download and install SSDT, either integrated into VS2012 as as a stand-alone. If you also want SQL server, then I'd use localDB (here).

Also, SSDT is two things - an improved replacement for 'Data Dude' AND the new BIDS - here's a blog with explanation.

Solution 3

"Microsoft Visual Studio 2010 does not support Business Intelligence Development Studio Integration Services, Report Services and Analysis Services projects for SQL Server 2008 and SQL Server 2008 R2. To work around this issue, you can install Visual Studio 2008 alongside Visual Studio 2010 on the same machine and then open the Business Intelligence Development Studio projects in Visual Studio 2008."

http://msdn.microsoft.com/en-us/library/ms173767%28v=sql.105%29.aspx

Guess this means: NO BIDS projects are possible under VS 2010.